Second/Third Most Recent Orders

[Guest]

I'm writing a query in which I want to idenify customers who are
"reactivated". Where reactivated means = an order in the current month(feb),
none in three months prior to current(jan-nov), but at least one in the the
three prior to those(oct-aug). I'm grouping by customer so I had to use Max
to get the most recent order date. How can I determine the second and third
most recent orders?

 

[Allen Browne]

Use 3 subqueries to figure out which clients have:
- order(s) this month, AND
- no order in the previous 3 months, AND
- order(s) in the prior 3 months.

The query will look something like this:

SELECT tblClient.* FROM tblClient WHERE EXISTS
( SELECT OrderID FROM tblOrder
WHERE tblOrder.ClientID = tblClient.ClientID
AND tblOrder.OrderDate > Date() - Day(Date()) )
AND NOT EXISTS
( SELECT ... )
AND EXISTS
( SELECT ... );

If subqueries are new, here is Microsoft's introduction:
http://support.microsoft.com/?id=209066

Other possible solutions described in this article:
http://www.mvps.org/access/queries/qry0020.htm

 

[Guest]

I'm trying to do this in an update query. Does your answer still apply? I'm
new to subqueries...

 

[Allen Browne]

If the subqueries are in the WHERE clause, the query should be updatable, so
I would expect it to work as an UPDATE query.