Maximum but one less

[GRIFFO]

How to identify second to last (in maximum) data

WIth data like this, with repeating Order Numbers (for example):

Area Code Order Open Date
123 2/1/2005
123 4/1/2005
234 5/6/2005
234 8/9/2005
234 15/9/2005

Is there any way to retrieve the 2nd to last order details? I know about
maximum and minimum, but basically I want maximum - 1.

That is return

Order Number Order Open Date
123 2/1/2005
234 8/9/2005


Any assistance would be appreciated.

 

[Ken Snell \(MVP\)]

You can use a subquery within a query to do this:

SELECT T.AreaCode, Max(T.[Order Open Date])
FROM YourTableName AS T
WHERE T.[Order Open Date] <
(SELECT Max(A.[Order Open Date]
FROM YourTableName AS A
WHERE A.AreaCode = T.AreaCode)
GROUP BY T.AreaCode;

 

[Allen Browne]

You have probably figured out how to use a Totals query to Group By the
[Area Code] and retrieve the Max of the [Order Open Date].

Use a subquery to exclude the last order date for each area code, so that
you get the 2nd last.

1. In query design view, add [Order Open Date] to the grid a 2nd time.

2. In the Total row under this field, choose:
WHERE
3. In the Criteria row, enter something like this (all on one line):
< (SELECT Max([Order Open Date]) AS MaxAreaDate
FROM [Table1] AS Dupe
WHERE Dupe.[Area Code] = [Table1].[Area Code])

This will show the Area Code and second last Order Open Date for areas that
have at least 2 orders.

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[GRIFFO]

Superb skills from Ken and Allen once again. Thanks very much, your ongoing
assistance to the users of this forum is admirable and has assited me so many
times.
--
Kind Regards

John (aka Griffo).

You have probably figured out how to use a Totals query to Group By the
[Area Code] and retrieve the Max of the [Order Open Date].

Use a subquery to exclude the last order date for each area code, so that
you get the 2nd last.

1. In query design view, add [Order Open Date] to the grid a 2nd time.

2. In the Total row under this field, choose:
WHERE
3. In the Criteria row, enter something like this (all on one line):
< (SELECT Max([Order Open Date]) AS MaxAreaDate
FROM [Table1] AS Dupe
WHERE Dupe.[Area Code] = [Table1].[Area Code])

This will show the Area Code and second last Order Open Date for areas that
have at least 2 orders.

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[Ken Snell \(MVP\)]

Drat -- typo:

SELECT T.AreaCode, Max(T.[Order Open Date])
FROM YourTableName AS T
WHERE T.[Order Open Date] <
(SELECT Max(A.[Order Open Date])
FROM YourTableName AS A
WHERE A.AreaCode = T.AreaCode)
GROUP BY T.AreaCode;

--

Ken Snell
<MS ACCESS MVP>

You can use a subquery within a query to do this:

SELECT T.AreaCode, Max(T.[Order Open Date])
FROM YourTableName AS T
WHERE T.[Order Open Date] <
(SELECT Max(A.[Order Open Date]
FROM YourTableName AS A
WHERE A.AreaCode = T.AreaCode)
GROUP BY T.AreaCode;

--

Ken Snell
<MS ACCESS MVP>