Return date help needed

[Pat_RI]

I have a table which contains a date column and what I want to return is the
2nd newest date for each specific Item. For example

Product Date
1 1/1/2008
1 12/11/07
1 12/1/07
1 1/1/04

How do I return the row with the 12/11/07 date. The majority of the
"Product" ids have several hundred rows with several hundred different
dates.

 

[Allen Browne]

You will need to learn about subqueries to achieve this.

1. Create a query using this table.

2. Depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

3. In the Total row under the Product field, accept Group By.
In the Total row under the Date, choose Min.

4. Drag the Date field into the grid a 2nd time.
In the total row under this field, choose Where.
In the Criteria row under this, enter an expression like this (as one line):
<> (SELECT Min([Date]) AS TheDate
FROM Table1 AS Dupe
WHERE Dupe.[Product] = Table1.[Product])

The criteria you just entered is a subquery.
If that's a new concept, see:
Subquery basics at:
http://allenbrowne.com/subquery-01.html

Please not that this excludes:
- any product with only one date,
- the oldest date value (even if 2 or more records have that value)

If your field really is named Date, you might want to rename it. In some
contexts, Access will misunderstand this for the system date. Here's a list
of the field names that can cause you problems:
http://allenbrowne.com/AppIssueBadWord.html

 

[John Spencer]

One version of SQL that may work to do this is

SELECT A.Product, Max(A.Date) as AlmostLatest
FROM YourTable as A INNER JOIN
(SELECT B.Product, Max(B.Date) as theDate
FROM YourTable as B
GROUP BY B.Product) as C
ON A.Product = C.Product
And A.Date <> C.TheDate
GROUP BY A.Product

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