Ooops try again - four different costs for each UPC

[Mikey]

Sorry, previous message should have read as follows:
Mikey
--------------
I have 33 UPC codes that will have four different prices attached to each
UPC depending on which head office number is selected (four separate head
offices)

Can anyone offer a better solution than a separate cost table for each head
office linked back to a head office table?

PRODUCTS_TABLE
UPC (PK)
NAME
DESCRIPTION
SIZE
PACK
COST

HEAD_OFFICE_ONE_TABLE
HEAD_OFFICE_# (FK)
UPC (PK)
CASE_COST

HEAD_OFFICE_TWO_TABLE
HEAD_OFFICE_# (FK)
UPC (PK)
CASE_COST

HEAD_OFFICE_THREE_TABLE
HEAD_OFFICE_# (FK)
UPC (PK)
CASE_COST

HEAD_OFFICE_ONE_TABLE
HEAD_OFFICE_# (FK)
UPC (PK)
CASE_COST

 

[tina]

putting data into table names (HeadOfficeOne, HeadOfficeTwo), is a no-no in
database design, just as is putting data into field names.

you have a many-to-many relationship between products and head offices: one
head office may be associated with many products, and one product may be
associated with many head offices. in a relational database model, a
many-to-many relationship is expressed with a child (linking) table that
forms the "many" side of a one-to-many relationship with each of the two
parent tables.

suggest three tables:

tblProducts (parent table)
UPC (pk)
the other fields you listed in your post. just make sure each field
describes the product itself, NOT the product in relation to an office.

tblHeadOffices (parent table)
HeadOfficeID (pk) (note: not a good idea to use any character except alphas
and underscore in a field name, or any name, in Access)
other fields that describe a head office itself, not the office in relation
to a product.

tblOfficeProducts (child [linking] table)
OfficeProductsID (pk)
HeadOfficeID (fk from tblHeadOffices)
UPC (fk from tblProducts)
CaseCost
other fields that describe a specific product in relation to a specific
office.

if you will never have the same product listed for the same office *more
than once* in tblOfficeProducts, then you can remove the OfficeProductsID
field and use both foreign key fields as a combination primary key for the
table.

hth