I am trying to figure out how, in the Northwind example, the prices go up in the product table, but the detail for past orders maintains the previous price.

When I change the price in my table, all my orders that I previously entered, the price changes as well.

Any help would be greatly appreciated!

I'm not familiar with the northwind database, but here's the idea:

You can make another table called PriceHistory, with the fields ProductKey,
Price, StartDate. The primairykey is the three columns together. The
pricefield is now no longer in the products table but in this table. Here
you can enter new prices with their startingdate so you keep a history of
all the prices. To join a price to a product you use:

SELECT
Products.Name,
Max(PriceHistory.Price) AS Price
FROM
Products INNER JOIN PriceHistory ON Products.Key =
PriceHistory.ProductKey
WHERE PriceHistory.StartDate<=Date()
GROUP BY Products.Name;

The same goed for orders, but then you join the orders table in there as
well and the whereclause becomes:
WHERE PriceHistory.Startdate<=Orders.OrderDate

Hope this helps,

Regards Gert-Jan

"kinberly" <(E-Mail Removed)> schreef in bericht
news:A12D9DFD-EF4F-4594-BDBB-(E-Mail Removed)...
> I am trying to figure out how, in the Northwind example, the prices go up
in the product table, but the detail for past orders maintains the previous
price.
>
> When I change the price in my table, all my orders that I previously
entered, the price changes as well.
>
> Any help would be greatly appreciated!

Oops, BIG Mistake. (it's been a while) Forget the previous message!

This is it:
Make a new tabel called PriceHistory with the following fields:
ProductKey Numeric :Foreignkey of the related product
StartDate Date/Time ate that the price was raised(or
lowered) to the price in this record
Price Currency :The price

Make the first two columns the primairy key. (don't forget or this will be a
slow query.) If you also make a relationship between the ProductKey in this
table and the Key column in the products table you get to use the nifty
subdatasheet feature so it's easier to use this all.

In this table, you keep a log of all the prices that ever were for this
product with their startingdate. For the current price of an item use the
following query:

SELECT
Products.Name,
PriceHistory.Price AS Price
FROM
Products INNER JOIN PriceHistory ON
Products.Key = PriceHistory.ProductKey
WHERE
PriceHistory.StartDate=(SELECT MAX(StartDate) FROM PriceHistory WHERE
PriceHistory.ProductKey = Products.Key AND StartDate<=Date());


When you want to find the price for an order, inner join products,
pricehistory, orders and orderlines (i presume it's like this) and the
instead of the "date()" function at the end type "Orders.Date" So you take
the price for the day the order was placed instead of today. Hope this
helps.

GJ



"Gert-Jan van der Kamp" <gjvdkamp[@spam]hotmail.com> schreef in bericht
news:boijdg$sjs$(E-Mail Removed)...
> I'm not familiar with the northwind database, but here's the idea:
>
> You can make another table called PriceHistory, with the fields
ProductKey,
> Price, StartDate. The primairykey is the three columns together. The
> pricefield is now no longer in the products table but in this table. Here
> you can enter new prices with their startingdate so you keep a history of
> all the prices. To join a price to a product you use:
>
> SELECT
> Products.Name,
> Max(PriceHistory.Price) AS Price
> FROM
> Products INNER JOIN PriceHistory ON Products.Key =
> PriceHistory.ProductKey
> WHERE PriceHistory.StartDate<=Date()
> GROUP BY Products.Name;
>
> The same goed for orders, but then you join the orders table in there as
> well and the whereclause becomes:
> WHERE PriceHistory.Startdate<=Orders.OrderDate
>
> Hope this helps,
>
> Regards Gert-Jan
>
> "kinberly" <(E-Mail Removed)> schreef in bericht
> news:A12D9DFD-EF4F-4594-BDBB-(E-Mail Removed)...
> > I am trying to figure out how, in the Northwind example, the prices go
up
> in the product table, but the detail for past orders maintains the
previous
> price.
> >
> > When I change the price in my table, all my orders that I previously
> entered, the price changes as well.
> >
> > Any help would be greatly appreciated!
>
>