Multiple records for same entry!

[IT]

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.

 

[tina]

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