How to structure tables with various size and prices

[Anne]

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.

 

[J. Goddard]

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

ProductID - primary key

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

Common Name
Botanical Name
Government Price
List Price

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

 

[Anne]

I knew there was a simple solution.
Thanks,

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