Previous Date

[Mark]

I'm looking for how to set up a query to return the record with the previous
date to the most recent date. Example:
ReturnDate:
11/15/03
12/12/03
2/4/04
4/5/04

The most recent date is 4/5/04. I need to return the record with 2/4/04.

Thanks!

Mark

[Tom van Stiphout]

On Wed, 07 Apr 2004 04:42:48 GMT, "Mark" <mm*****@earthlink.net>
wrote:

I solved it in two steps, using a similar example from the Northwind
sample application.

1: Create query qryMaxOrderDate:
SELECT Orders.CustomerID, Max(Orders.OrderDate) AS MaxOfOrderDate
FROM Orders
GROUP BY Orders.CustomerID;

2: Create final query:
SELECT Orders.CustomerID, Max(Orders.OrderDate) AS SecondToLastDate
FROM qryMaxOrderDate INNER JOIN Orders ON qryMaxOrderDate.CustomerID =
Orders.CustomerID
WHERE (((Orders.OrderDate)<>[MaxOfOrderDate]))
GROUP BY Orders.CustomerID;

-Tom.

I'm looking for how to set up a query to return the record with the previous
date to the most recent date. Example:
ReturnDate:
11/15/03
12/12/03
2/4/04
4/5/04

The most recent date is 4/5/04. I need to return the record with 2/4/04.

Thanks!

Mark

[CDB]

This is another approach:

SELECT TOP 1 *
FROM [SELECT TOP 2 SomeTable.ReturnDate As RDt
FROM SomeTable
ORDER BY SomeTable.ReturnDate DESC]. As A
ORDER BY A.RDt;

The sub-query notation is for A97/Jet, but also works in A2K/Jet.

Clive
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:el********************************@4ax.com...

On Wed, 07 Apr 2004 04:42:48 GMT, "Mark" <mm*****@earthlink.net>
wrote:

I solved it in two steps, using a similar example from the Northwind
sample application.

1: Create query qryMaxOrderDate:
SELECT Orders.CustomerID, Max(Orders.OrderDate) AS MaxOfOrderDate
FROM Orders
GROUP BY Orders.CustomerID;

2: Create final query:
SELECT Orders.CustomerID, Max(Orders.OrderDate) AS SecondToLastDate
FROM qryMaxOrderDate INNER JOIN Orders ON qryMaxOrderDate.CustomerID =
Orders.CustomerID
WHERE (((Orders.OrderDate)<>[MaxOfOrderDate]))
GROUP BY Orders.CustomerID;

-Tom.

I'm looking for how to set up a query to return the record with the previousdate to the most recent date. Example:
ReturnDate:
11/15/03
12/12/03
2/4/04
4/5/04

The most recent date is 4/5/04. I need to return the record with 2/4/04.

Thanks!

Mark

[Nikos Yannacopoulos]

Mark,

From your phrasing I understand you want to set up a query in design view,
not programatically... which means ve posted to the wrong newsgroup. Anyway,
here it goes:

Make a simple select query on your table. Set the sort order on ReturnDate
to Descending, type the following criterion under ReturnDate:
< DMax("[ReturnDate]","TableName")
substituting TableName with the actual table name, and right-click in the
upper half of the design view (source tables area) to open the query
properties. Change the Top Values property from default All to 1.
You should be ready.

HTH,
Nikos

"Mark" <mm*****@earthlink.net> wrote in message
news:cF******************@newsread2.news.atl.earth link.net...

I'm looking for how to set up a query to return the record with the previous date to the most recent date. Example:
ReturnDate:
11/15/03
12/12/03
2/4/04
4/5/04

The most recent date is 4/5/04. I need to return the record with 2/4/04.

Thanks!

Mark

[David W. Fenton]

"CDB" <al***@delete.wave.co.nz> wrote in
news:c5**********@news.wave.co.nz:

This is another approach:

SELECT TOP 1 *
FROM [SELECT TOP 2 SomeTable.ReturnDate As RDt
FROM SomeTable
ORDER BY SomeTable.ReturnDate DESC]. As A
ORDER BY A.RDt;

The sub-query notation is for A97/Jet, but also works in A2K/Jet.

What about an non-equi join, on B.ReturnDate < A.ReturnDate? Then
you can apply criteria easily (even with a paramater), which you
can't do with the "virtual" table approach above.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Mark]

David,

Could you show what you mean.

Thanks!

Mark
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"CDB" <al***@delete.wave.co.nz> wrote in
news:c5**********@news.wave.co.nz:

This is another approach:

SELECT TOP 1 *
FROM [SELECT TOP 2 SomeTable.ReturnDate As RDt
FROM SomeTable
ORDER BY SomeTable.ReturnDate DESC]. As A
ORDER BY A.RDt;

The sub-query notation is for A97/Jet, but also works in A2K/Jet.

What about an non-equi join, on B.ReturnDate < A.ReturnDate? Then
you can apply criteria easily (even with a paramater), which you
can't do with the "virtual" table approach above.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Mark" <mm*****@earthlink.net> wrote in
news:%W***************@newsread3.news.atl.earthlin k.net:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"CDB" <al***@delete.wave.co.nz> wrote in
news:c5**********@news.wave.co.nz:

> This is another approach:
>
> SELECT TOP 1 *
> FROM [SELECT TOP 2 SomeTable.ReturnDate As RDt
> FROM SomeTable
> ORDER BY SomeTable.ReturnDate DESC]. As A
> ORDER BY A.RDt;
>
> The sub-query notation is for A97/Jet, but also works in
> A2K/Jet.

What about an non-equi join, on B.ReturnDate < A.ReturnDate? Then
you can apply criteria easily (even with a paramater), which you
can't do with the "virtual" table approach above.

Could you show what you mean.

Well, if you've got a join:

SELECT TOP 1 A.ReturnDate As Date1, B.ReturnDate As Date2
FROM SomeTable AS A INNER JOIN SomeTable AS B On A.ReturnDate <
B.ReturnDate WHERE A.ReturnDate=#1/1/2004#
ORDER BY A.ReturnDate, B.ReturnDate;

Something like that.

The method for doing this is to create the join with the QBE, then
in SQL view, change the "=" to < or > (depending on which you want.
You then won't be able to view the query in the QBE, but it will do
what you want.

The result will be a single row with your the date you filtered for
in the WHERE clause (which could then be parameterized), and the
second column being the next date greater than the date you filtered
on.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc