four different costs for each UPC

[Mikey]

Hi Folks:

I have 33 UPC codes that will have four different prices attached to each
UPC depending on which one of four head offices is selected

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

Any assistance would be appreciated


PRODUCTS_TABLE
UPC (PK)
NAME
DESCRIPTION
SIZE
PACK
COST

HEAD_OFFICE_ONE_TABLE (PK)
HEAD_OFFICE_# (FK)
UPC (PK)
CASE_COST

HEAD_OFFICE_TWO_TABLE (PK)
HEAD_OFFICE_# (FK)
UPC (PK)
CASE_COST

HEAD_OFFICE_THREE_TABLE (PK)
HEAD_OFFICE_# (FK)
UPC (PK)
CASE_COST

HEAD_OFFICE_FOUR_TABLE (PK)
HEAD_OFFICE_# (FK)
UPC (PK)
CASE_COST


to reply directly remove the "nospamnoway" from my return address

Thanks again.

Mikey

 

[John Vinson]

Hi Folks:

I have 33 UPC codes that will have four different prices attached to each
UPC depending on which one of four head offices is selected
Ok...

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

Yes indeed. Storing data in a tablename is a VERY bad idea. What if
the company expands and you have to deal with seven offices!?

Any assistance would be appreciated

PRODUCTS_TABLE
I'd suggest just calling it Products. You know it's a table. <g>
UPC (PK)
NAME
NAME is a reserved word in Access and may give you trouble; maybe
call this ProductName.
DESCRIPTION
SIZE
PACK
COST

Offices
OfficeNo
OfficeName <e.g. "Bristol", "Poughkeepsie"
<any other needed info about the office as an entity>

Pricing
OfficeNo ' FK: which office
UPC ' FK: which product
CaseCost

If there is a product sold out of four offices, there would be four
records in this table.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps