How to structure tables with various size and prices

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have a nursery and I am trying to evaluate the inventory for the
government crop insurance.
My Table structure for the plants is a follows
ProductID - primary key
Common Name
Botanical Name
Government Price 72 cell liner
Government Price 50 cell liner
Government Price 2 Quart Pot
Government Price 3 Gal Pot
List Price 72 cell liner
List Price 50 cell liner
List Price 2 Quart Pot
List Price 3 Gal Pot
The prices are very specific for each item.

I need to attach this table to my inventory count and I am not sure how to
get the correct prices. Want to create a report, which shows my inventory
with 2 columns for prices, showing the government price and list price,
depending on size.
I think I need to split the above table.
 
Redesign the pricing table. Each entry should be for only one size:
ProductID - primary key
Click to expand...
Size - Primary key <-- This is a new field
Common Name
Botanical Name
Government Price
List Price
Click to expand...

The inventory (presumably) will be a count of each size of each plant:

ProductID - primary key
Size - Primary key
quantity

Now it's easy to create a query linking the two tables on ProductID and
Size, and calculating the inventory value. the report can be based on
that query.

John
 
I knew there was a simple solution.
Thanks,

J. Goddard said:
Redesign the pricing table. Each entry should be for only one size:

Size - Primary key <-- This is a new field

The inventory (presumably) will be a count of each size of each plant:

ProductID - primary key
Size - Primary key
quantity

Now it's easy to create a query linking the two tables on ProductID and
Size, and calculating the inventory value. the report can be based on
that query.

John
Click to expand...
 
Back
Top