School Print Shop Database

[twodoor55]

I have been working on putting together a school print shop database. I am
having trouble understanding how to keep order history. If I make a change
in my products table, it cascades to the old records.

I know that there is an order template, but I need to build it myself to
understand how to fix it when there is a problem.

I am not quite sure how to do this. Help!

Thanks

 

[twodoor55]

Does that mean, I can't use a look up table and I have to physically put the
cost into the table. Because, I thought if I used a look up table there
would be no chance for error.

Confused on this part.

 

[Ken Snell MVP]

There are two approaches to this.

One, as Steve suggests, is to store the actual price *At The Time Of The
Order* in the table that holds the items for an order.

Two, add a field to your lookup table for Prices, and name this field
EffectiveDate. Use the field to store the date on which that price became
effective. Then, you can use the date of the order to look up the price that
was in effect at that time:

SELECT Top 1 Price
FROM LookUpTable
WHERE EffectiveDate <= [OrderDate]
ORDER BY EffectiveDate DESC;