If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Building a shareholder database using access 2007
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. |
#2
|
|||
|
|||
Building a shareholder database using access 2007
On Wed, 22 Oct 2008 20:49:00 -0700, nerak
wrote: 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.) -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Building a shareholder database using access 2007
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" wrote: On Wed, 22 Oct 2008 20:49:00 -0700, nerak wrote: 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.) -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Building a shareholder database using access 2007
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] |
#5
|
|||
|
|||
Building a shareholder database using access 2007
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] |
#6
|
|||
|
|||
Building a shareholder database using access 2007
On Thu, 23 Oct 2008 18:15:00 -0700, nerak
wrote: 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 -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Building a shareholder database using access 2007
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 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" wrote: On Thu, 23 Oct 2008 18:15:00 -0700, nerak wrote: 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 -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Building a shareholder database using access 2007
On Thu, 23 Oct 2008 23:11:01 -0700, nerak
wrote: 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 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???? hank you very much for your time and assistance so far. -- John W. Vinson [MVP] |
#9
|
|||
|
|||
Building a shareholder database using access 2007
I have added to your answers below.
"John W. Vinson" wrote: On Thu, 23 Oct 2008 23:11:01 -0700, nerak wrote: 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. Even if the column contact person for both was Bill Gates? The tables I have for investors are Table 1 Table2ID First Name Second Name Phone No Mobile 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. Shares held in name of ATF Trust ACN (company No) Virtuoffice Pty Ltd John Grange family Trust 37622594 Table 3 Table1ID Table2ID Address City State Postcode 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. 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. 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? 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? 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. 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???? This table would be linked to the query that brings my Investors together. hank you very much for your time and assistance so far. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|