four different costs for each UPC

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mikey - 07 Dec 2004 23:24 GMT

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

Reply to this Message

John Vinson - 08 Dec 2004 03:03 GMT

>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

Reply to this Message