Previous Date

[Kirk P.]

My data looks like this:

RunDate Customer SoldDate
03/26/2007 ABC 02/26/2007
03/26/2007 ABC 03/15/2007
04/09/2007 ABC 03/31/2007
04/09/2007 ABC 03/28/2007
04/09/2007 ABC 04/05/2007

Obviously I can retrieve records associated with a RunDate of 4/9/2007 by
selecting the max date, however I want to select the records associated with
the RunDate just prior to the max date, in this case 3/26/2007. Any way to
do this without actually creating a form to let the user select the date?
I'd prefer it automatically pick the date just prior to the max date.

 

[Ofer Cohen]

Try something like

SELECT TableName.RunDate
FROM TableName
WHERE TableName.RunDate In (Select Top 2 RunDate From TableName Group By
RunDate Order By RunDate Desc)

 

[Ofer Cohen]

If you want to include only the date before last, without the last record, try

SELECT TableName.RunDate
FROM TableName
WHERE TableName.RunDate In (Select max (RunDate) From TableName Where
RunDate Not In (Select Max( RunDate) From TableName))

 

[John Spencer]

By customer? Perhaps something like the following

SELECT T1.RunDate, T1.Customer, T1.SoldDate
FROM YourTable as T1
WHERE T1.RunDate =
(SELECT Max(T2.RunDate)
FROM YourTable as T2
WHERE T2.RunDate <
(SELECT Max (T3.RunDate)
FROM YourTable as T3
WHERE T3.Customer = T1.Customer)
And T2.Customer = T1.Customer)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

My data looks like this:

RunDate Customer SoldDate
03/26/2007 ABC 02/26/2007
03/26/2007 ABC 03/15/2007
04/09/2007 ABC 03/31/2007
04/09/2007 ABC 03/28/2007
04/09/2007 ABC 04/05/2007

Obviously I can retrieve records associated with a RunDate of 4/9/2007 by
selecting the max date, however I want to select the records associated with
the RunDate just prior to the max date, in this case 3/26/2007. Any way to
do this without actually creating a form to let the user select the date?
I'd prefer it automatically pick the date just prior to the max date.

Ofer's suggestion will give you the most recent date and also the previous
date; a double subquery will give only the penultimate record:

SELECT [RunDate], [Customer], [SoldDate]
FROM yourtable
WHERE RunDate =
(SELECT Max([RunDate]) FROM yourtable AS X
WHERE X.[Customer] = yourtable.[Customer]
AND X.[Rundate] <
(SELECT Max([Rundate]) FROM yourtable AS Y
WHERE Y.[Customer] = yourtable.[Customer]));

John W. Vinson [MVP]

 

[Kirk P.]

That helps - thanks!

If you want to include only the date before last, without the last record, try

SELECT TableName.RunDate
FROM TableName
WHERE TableName.RunDate In (Select max (RunDate) From TableName Where
RunDate Not In (Select Max( RunDate) From TableName))

 

[KARL DEWEY]

I used three queries. The first pulls the top (max Rundate). The second
pulls a Rundate lessthan the top. The third pulls data matching the second
Rundate.
Query named Kirk_Top --
SELECT TOP 1 TableName.RunDate
FROM TableName
GROUP BY TableName.RunDate
ORDER BY TableName.RunDate DESC;

Query named Kirk_Second --
SELECT TOP 1 TableName.RunDate
FROM TableName, Kirk_Top
GROUP BY TableName.RunDate, Kirk_Top.RunDate
HAVING (((TableName.RunDate)<[Kirk_Top].[RunDate]))
ORDER BY TableName.RunDate DESC;


SELECT TableName.RunDate, TableName.Customer, TableName.SoldDate
FROM TableName, Kirk_Second
WHERE (((TableName.RunDate)=[Kirk_Second].[RunDate]));

 

[AccessDB.Info]

This might be helpful

Previous Date & Sum of Month/Year to-date using a correlated subquery
http://www.accessdb.info/content/view/126/43/