School Print Shop Database

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

It sounds like you are describing the typical problem with product price.
This problem is overcome by adding another field named ProductPrice to your
order detail table and recording the actual product price in ProductPrice.

Steve



"twodoor55" wrote in message
...
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

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.

"Steve" wrote:

It sounds like you are describing the typical problem with product price.
This problem is overcome by adding another field named ProductPrice to your
order detail table and recording the actual product price in ProductPrice.

Steve



"twodoor55" wrote in message
...
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

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;

--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/


"twodoor55" wrote in message
...
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.

"Steve" wrote:

It sounds like you are describing the typical problem with product price.
This problem is overcome by adding another field named ProductPrice to
your
order detail table and recording the actual product price in
ProductPrice.

Steve



"twodoor55" wrote in message
...
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