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*****@earthl ink.net>
wrote:

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

1: Create query qryMaxOrderDate :
SELECT Orders.Customer ID, Max(Orders.Orde rDate) AS MaxOfOrderDate
FROM Orders
GROUP BY Orders.Customer ID;

2: Create final query:
SELECT Orders.Customer ID, Max(Orders.Orde rDate) AS SecondToLastDat e
FROM qryMaxOrderDate INNER JOIN Orders ON qryMaxOrderDate .CustomerID =
Orders.Customer ID
WHERE (((Orders.Order Date)<>[MaxOfOrderDate]))
GROUP BY Orders.Customer ID;

-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.Retur nDate As RDt
FROM SomeTable
ORDER BY SomeTable.Retur nDate 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.spa m.cox.net> wrote in message
news:el******** *************** *********@4ax.c om...

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

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

1: Create query qryMaxOrderDate :
SELECT Orders.Customer ID, Max(Orders.Orde rDate) AS MaxOfOrderDate
FROM Orders
GROUP BY Orders.Customer ID;

2: Create final query:
SELECT Orders.Customer ID, Max(Orders.Orde rDate) AS SecondToLastDat e
FROM qryMaxOrderDate INNER JOIN Orders ON qryMaxOrderDate .CustomerID =
Orders.Customer ID
WHERE (((Orders.Order Date)<>[MaxOfOrderDate]))
GROUP BY Orders.Customer ID;

-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]","TableNam e")
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*****@earthl ink.net> wrote in message
news:cF******** **********@news read2.news.atl. earthlink.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.w ave.co.nz> wrote in
news:c5******** **@news.wave.co .nz:

This is another approach:

SELECT TOP 1 *
FROM [SELECT TOP 2 SomeTable.Retur nDate As RDt
FROM SomeTable
ORDER BY SomeTable.Retur nDate 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********@bwa y.net.invalid> wrote in message
news:Xn******** *************** ***********@24. 168.128.86...

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

This is another approach:

SELECT TOP 1 *
FROM [SELECT TOP 2 SomeTable.Retur nDate As RDt
FROM SomeTable
ORDER BY SomeTable.Retur nDate 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*****@earthl ink.net> wrote in
news:%W******** *******@newsrea d3.news.atl.ear thlink.net:

"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:Xn******** *************** ***********@24. 168.128.86...

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

> This is another approach:
>
> SELECT TOP 1 *
> FROM [SELECT TOP 2 SomeTable.Retur nDate As RDt
> FROM SomeTable
> ORDER BY SomeTable.Retur nDate 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