Relateive maximum/ price value of a date

  • Thread starter Thread starter Daryl Au
  • Start date Start date
D

Daryl Au

Dear MVPs,

I've a table showing the selling price of a product of every day, examples
are as following:

product, date, price:
egg, 2008/01/01, $1;
egg, 2008/01/02, $1.5;
egg, 2008/01/03, $2;
egg, 2008/01/04, $1.5;
egg, 2008/01/05, $2;
egg, 2008/01/06, $2.5;
egg, 2008/01/07, $3;
....

I would like to make a query showing the history maximum/ highest price, as
follows:

product, date, history highest price: (remark by me
for your ref)
egg, 2008/01/01, $1;
egg, 2008/01/02, $1.5; ($1.5 of
01/02 is higher than $1of 01/01)
egg, 2008/01/03, $2;
egg, 2008/01/04, $2; ($2 of
01/03 is higher than $1.5 of 01/04)
egg, 2008/01/05, $2;
egg, 2008/01/06, $2.5;
egg, 2008/01/07, $3;
...

How can I make it? Thank you so much

Best Regards,
Daryl
 
A correlated subquery should work

SELECT YourTable.Product, YourTable.Date, YourTable.Price
,(SELECT Max(Price)
FROM YourTable as Temp
WHERE Temp.Product = YourTable.Product
AND Temp.Date <= YourTable.Date) as HistoryHigh
FROM YourTable



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top