select price decreases

  • Thread starter Thread starter carol
  • Start date Start date
C

carol

table setup:

item#, price, date - table with mutilple prices and
dates for each item.

need to query only items where most recent date's price is
less than previous price. can you help?
 
table setup:

item#, price, date - table with mutilple prices and
dates for each item.

need to query only items where most recent date's price is
less than previous price. can you help?
Click to expand...

A Self Join query would work here. Add the table to the query design
window *twice*, joining by Item#. Access will alias the second
instance by adding _1 to the table name.

Put a criterion on [Table_1].[Date] (which you might want to consider
renaming, it's a reserved word and Access *will* get it confused with
the Date() function someday!) of

=DMax("[Date]", "
", "[Date] < #" &
.[Date] & "#")

to select the most recent prior date;

and on [Table_1].[Price] of

<
.[Price]
 
Hi,



SELECT a.ItemID, a.DateTime, LAST(a.Price),
b.DateTime, LAST(b.Price)

FROM (myTable As a INNER JOIN myTable As b
ON a.ItemID=b.ItemID AND a.DateTime>b.DateTime)
INNER JOIN myTable As c
ON a.ItemID=c.ItemID AND a.DateTime>c.DateTime

GROUP BY a.ItemID, a.DateTime, b.DateTime

HAVING b.DateTime=MAX(c.DateTime)
AND LAST(a.Price) < LAST(b.Price)



should do. Clearly, c.DateTime are the date occurring before a.DateTime. If
we keep only records having b.DateTime = MAX(c.DateTime), we kept only the
record occurring before a.DateTime. It is then a matter of simplicity to
read the corresponding value in a.Price and b.Price.


Hoping it may help,
Vanderghast, Access MVP
 
Hi,


... and a more portable alternative (for MS SQL Server, but still
working for Jet too):


SELECT a.ItemID, a.DateTime, a.Price,
b.DateTime, b.Price

FROM (myTable As a INNER JOIN myTable As b
ON a.ItemID=b.ItemID)
INNER JOIN myTable As c
ON a.ItemID=c.ItemID AND a.DateTime>c.DateTime

GROUP BY a.ItemID, a.DateTime, b.DateTime,
a.Price, b.Price

HAVING b.DateTime=MAX(c.DateTime)
AND a.Price < b.Price




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top