Table Design Help

[dbspl1]

I am the business manager for an automotive collision repair facility and am
attempting to track all the supplies we order. The number of supplies would
be too much for Quickbooks to handle so I'm trying to use Access to track
them. I have four tables thus far: Order Details, Orders, Products,
Suppliers. Our prices fluctuate greatly, in many cases from day to day, and
this causes me concern. If the price changes for X product in February from a
January amount of $100 to $200, will that change appear if I run reports and
queries for January? That is, will the January price for X product show as
$200 instead of $100? Do I need to create additional tables to handle these
fluctuations? Thanks.

 

[Bill Edwards]

In the OrderDetails table include the item price in order to keep track of
the "historical" price.

 

[Brian]

I would recommend that you track the price in a field in the Order
Details table. Each entry in Order details would have the Product,
Order number, supplier, and price. Product and Supplier could be combo
boxes that are choosen from a list in the Products and Suppliers
Tables. This would enable your reports to function from month to month
without adjusting previous months.

Hope this helps.

Bp

 

[dbspl1]

Thanks for your reply Bill. Another question though. My original plan was to
include the price in the Product table, but your answer has made me rethink
that. Should I keep the price field in the Product table and establish a
relationship to the Order Detail table or should I remove the price field
from Product and keep it solely in Order Detail?

 

[dbspl1]

Yes, Brian, that does help. Thanks for your quick reply. I'm very new to
Access and appreciate all the help.

 

[Bill Edwards]

Keep it in both. The current price is in the Product table. The price at
time of sale is in the OrderDetail table.
When creating an orderdetail line the user would select the product; the
product price from the product table would then be used to provide a value
for the OrderDetail unit price (which may also allow manual editing at the
time the orderdetail line is created).