Hi John

Thanks for the explanation, now I know why I kept going round in circles.

This makes sense that I need to get to these three tables

Company
Investor
Shares

However my Investor table would have many double ups if I left it as one
table because some investors have various companies that they hold shares
under as well as under their own name. In other words I need to break the
investor table into a few tables and bring them back as one. What is the best
way to deal with this? I would think a few tables then a query, is this
correct or am I on the wrong track again?

Thanks for your help
nerak

"John W. Vinson" wrote:

On Wed, 22 Oct 2008 22:44:01 -0700, nerak
wrote:

Thanks John

What do you mean by a holdings table?

I will try to clarify below.

I have a table for all the company details and another with the directors of
the company because some companies have the same directors.

For the shareholders I have three main tables

Table 1 Shareholder details that aren't repeated (this covers number of
shares, Company name, etc)
Table 2 Addresses
Table 3 Personal details (First name, last name etc)

Each of these shareholder tables are many to many because I can have an
individual who has many addresses depending on which company he has shares
in, also a company with the same person and different address.

I need eventually to be able to run reports as to who are the shareholders
in each company. Who they sold their shares to and when.

Each time I think I have a one to many relationship I find it is really a
many to many.

That's very typical. In the real world many to many relationships are almost
universal. In database terms thouch each such relationship must be
"decomposed" into two or more one to many relationships; this usually requires
creating another table to model the RELATIONSHIP.

Your structure for shareholders is still incorrect.

I'm a shareholder. I've got a brokerage account; I own a bunch of different
stocks and mutual funds. (I don't own nearly as much value as I owned a month
ago but that's not the issue here... sigh...)

One of the stocks I own is WalMart. Lots of other people own WalMart too.

If I had a table of stock ownership, I'd certainly have a table of companies,
and WalMart would be one of them. However there would be nothing in the
Companies table about me, or indicating that I own any shares of WalMart; my
ownership of the stock is not an attribute of WalMart Corporation, and they'll
keep going whether I keep my stock or sell it!

If my database tracks several stockholders, not just me, then the same
applies. I've got a name, address, other contact information that needs to be
in the Stockholders table; however, the Stockholders table should be just a
table *of people* (or institutions perhaps). Maybe you want to call this
entity an Investor instead. There should be nothing in this table about what
stocks are owned, because an individual investor might own one stock, or
sixty, or maybe none at all (the wise one who went to a 100% cash position
before the crash).

You need a third table to record who owns what: it would have a foreign key to
the Investors table (who owns something), and another foreign key to the
Stocks table (what do they own); and it would have other fields to record how
much this investor owns of this stock, when they bought it, for how much, etc.

And you'll need yet another table for stock transactions. A sale or purchase
of stock is a valid entity; it has to have links to Investor (who sold it),
Stocks (what they sold), and fields for quantity, price, etc.

Hope this clarifies what I'm talking about...
--

John W. Vinson [MVP]