Building a shareholder database using access 2007

[nerak]

I am trying to build a database for shareholders and the companies they are
shareholders to. It has been quite a while since I build my last database and
2007 is very different to what I last used. I undersand I need enough tables
so I don't repeat entries and also that I need to relate the tables to be
able to build queries or forms. However, as I have many to many relationships
as well as one to many I am confused as to where to use queries and even if I
need to.

I have two tables on the company side and about five on the shareholder
side. Ideally I would like one form to enter information for the shareholder
and another for the company but it looks like that may not be possible.

I am book for a training course in Access 2008 but it is two months away and
I am being pushed to deliver the database asap.
Any suggestions or help would be greatly appreciated.

 

[John W. Vinson]

I am trying to build a database for shareholders and the companies they are
shareholders to. It has been quite a while since I build my last database and
2007 is very different to what I last used. I undersand I need enough tables
so I don't repeat entries and also that I need to relate the tables to be
able to build queries or forms. However, as I have many to many relationships
as well as one to many I am confused as to where to use queries and even if I
need to.

I have two tables on the company side and about five on the shareholder
side. Ideally I would like one form to enter information for the shareholder
and another for the company but it looks like that may not be possible.

I am book for a training course in Access 2008 but it is two months away and
I am being pushed to deliver the database asap.
Any suggestions or help would be greatly appreciated.

I don't understand what your tables are. Each type of Entity - a stock, a
company, a shareholder - should have one table. What are your five shareholder
tables??

A Many to Many relationship is simply two one to many relationships: if each
shareholder owns many stocks, and each stock may be owned by many
shareholders, you need a Holdings table with fields for the ShareholderID, the
stock ID (CUSIP probably would be a good primary key for the stock table), and
other fields pertinent to the ownership (number of shares, date acquired,
perhaps cost basis, etc.)

 

[nerak]

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.

 

[John W. Vinson]

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...

 

[nerak]

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]

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?

I'm no expert in brokerages, but I would guess that each stock has an owner of
record - which might be an individual, or a company; but only one owner...
right?

What you probably need is a "family relationships" structure linked to
Investor. I'm groping in the dark here and may not be accurately modeling the
real world situation, but I can imagine an InvestorRelationships table. For
example you might have

Investors
123 Bill Gates
234 Microsoft Corp.
456 Gates Foundation


and a table linking these to one another:

InvestorRelationships

FromID ToID Relationship
123 234 Employee
123 456 Trustee
234 456 Donor

 

[nerak]

Thanks John

Looking at your investors table
If Bill Gates bought shares in his own name but also in his company name
could these both go in the same table?

The tables I have for investors are

Table 1
Table2ID
First Name
Second Name
Phone No
Mobile
Email

Table 2
Shares held in name of (If company involved)
ATF Trust
ACN

Table 3
Table1ID
Table2ID
Address
City
State
Postcode

I have broken them up like this to avoid doubling up with input. I now need
to bring them together as one so I can relate them to the other two tables.
Is this best done with a table or query?

These are not what I have called the tables.

My Company table has:
Company name
ACN
TFN
Incorp Date
GST reg
Review Date......

My Shares table has:
Number of shares
paid value
percentage held
Share numbers
Certificate Number.....

hank you very much for your time and assistance so far.

nerak

 

[John W. Vinson]

Thanks John

Looking at your investors table
If Bill Gates bought shares in his own name but also in his company name
could these both go in the same table?

Yes, with different ownerID's: one for the person, the other for the company.

The tables I have for investors are

Table 1
Table2ID
First Name
Second Name
Phone No
Mobile
Email

Table 2
Shares held in name of (If company involved)
ATF Trust
ACN

Do you mean you have a FIELD for ATF Trust, and another field for ACN? That's
*completely incorrect*. You don't store data in fieldnames.

Table 3
Table1ID
Table2ID
Address
City
State
Postcode

What real-life Entity does this table represent?

I have broken them up like this to avoid doubling up with input. I now need
to bring them together as one so I can relate them to the other two tables.
Is this best done with a table or query?

Anytime you want to bring data from two or more tables together you use a
Query. That's what queries *do*. Tables don't "do" anything other than store
data.

These are not what I have called the tables.

My Company table has:
Company name
ACN
TFN
Incorp Date
GST reg
Review Date......

You're speaking jargon that I don't know. What are ACN? TFN? GST?

My Shares table has:
Number of shares
paid value
percentage held
Share numbers
Certificate Number.....

But no indication of WHAT company's shares these are, nor who holds them????

 

[nerak]

I have added to your answers below.

Yes, with different ownerID's: one for the person, the other for the company.

Even if the column contact person for both was Bill Gates?

Do you mean you have a FIELD for ATF Trust, and another field for ACN? That's
*completely incorrect*. You don't store data in fieldnames.

Shares held in name of ATF Trust
ACN (company No)
Virtuoffice Pty Ltd John Grange family Trust
37622594

What real-life Entity does this table represent?

This table hold the addresses for the entities for when I need to send
shareholder mailouts. They may be for table 1 or table 2 depending on who
holds the shares.

Anytime you want to bring data from two or more tables together you use a
Query. That's what queries *do*. Tables don't "do" anything other than store
data.

I think this just answered my main problem for me.

I will use a query to bring my Investor information together.

Make a relationship between the Investor query, my Company Table and Shares
table.

I used to think of queries for formulas in something but obviously they are
broader than that.

Am I on the right track here?

You're speaking jargon that I don't know. What are ACN? TFN? GST?

ACN (Company Number) TFN (Tax File Number) GST reg (date registered for GST)

All these companies are owned by us and we need to keep them up to date.

But no indication of WHAT company's shares these are, nor who holds them????

This table would be linked to the query that brings my Investors together.