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
|
|||
|
|||
Multiple records for same entry!
I am currently working on a database that stores information on 400+
companies. Problem is: Companies that have more than one location/address have different records for the headquarters, subsidiary, sales office etc. I end up having 2-6 records for, say, Company A. When I update the sales figure for Company A, I need to do it 6 times. How can I merge the records for Company A or somehow specify a master records for COmpany A? I would basically like Access to auto-update each record by just entering the data once on a "master" record for Company A. Any advice would be greatly appreciated. |
#2
|
|||
|
|||
suggest the following:
tblCompanies CoID (primary key) CoName other specific information about the company as a whole tblOfficeCategories CatID (primary key) CatName (categories such as headquarters, subsidiary, sales office, etc) tblCompanyOffices OffID (primary key) CoID (foreign key from tblCompanies) CatID (foreign key from tblOfficeCategories StreetAddress City State Zip (other specific information that describes a company office) the parent table is tblCompanies and its' child table is tblCompanyOffices, with a one-to-many relationship: one company can have many offices, and each office belongs to only one company. tblOfficeCategories is a lookup (i call it "supporting") table for tblCompanyOffices. *note: make sure you do NOT create a Lookup field in tblCompanyOffices. see http://www.mvps.org/access/lookupfields.htm for more information.* it's likely that your "sales figures" for each company do not belong in tblCompanies, but rather in a child table. without more information, though, i can only suggest something along the lines of tblCompanySales SalesID (primary key) CoID (foreign key from tblCompanies) (other specific fields that describe the sales data, such as Year, Month or Quarter, gross sales amount, expenses amount.) note: you notice i didn't include "net" amount. that's usually a calculated value, and if you store the elements of the calculation - such as gross and expenses - you normally don't store the result of the calculation as hard data. also note: be careful about using Access reserved words as names in Access; it can cause a lot of problems. Year and Month are two examples of reserved words. hth "IT" wrote in message news I am currently working on a database that stores information on 400+ companies. Problem is: Companies that have more than one location/address have different records for the headquarters, subsidiary, sales office etc. I end up having 2-6 records for, say, Company A. When I update the sales figure for Company A, I need to do it 6 times. How can I merge the records for Company A or somehow specify a master records for COmpany A? I would basically like Access to auto-update each record by just entering the data once on a "master" record for Company A. Any advice would be greatly appreciated. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
multiple records on a form | ndunwoodie | New Users | 6 | October 20th, 2004 03:35 PM |
Newbie: Multiple records to one customer | Bill Craig | Database Design | 3 | June 18th, 2004 05:07 PM |
Showing multiple records on one page of a form | Design by Sue | General Discussion | 0 | June 17th, 2004 02:20 PM |