First and second occurence

[jamphan]

I have a list of accounts with transaction dates. The account could be in the list 10 or more times with a different transaction date. What I need to do is get the most recent transaction date then the next most recent transaction date. I am drawing a blank as to how I can get the second most recent date. Ex:

Acct Date
123 8/1/2010
123 5/1/2010
123 4/1/2010

From this I would want to extract the 8/1/2010 and 5/1/2010 but obviously it wouldn't be this clear cut as to what the second date would be since it is about 80,000 total accounts/dates



Any ideas? Thanks!

[weekend00]

select top 2 [Date] from table group by [date] order by [date] desc

[jamphan]

The syntax is not working. Could you please give a little more detail?

[jamphan]

I should also metion I need to return the account number as well as the date. Ex:

Acct Date
123 8/1/2010
123 5/1/2010
123 4/1/2010
456 12/1/2009
456 10/1/2008
456 8/1/2007

So I would need returned:

123 8/1/2010
123 5/1/2010
456 12/1/2009
456 10/1/2008

[weekend00]

In that case, you need VBA code to do it. I just give you the idea but am too lazy to program the code.

[jamphan]

I see. Anyone care to share how to do this?

[weekend00]

Please try following code.

Please create a table TopRecord with the 2 fields (e.g. Acct, [Date])(if you don't like this name, you can choose other name, but you must change the code accordingly), and modify following items to match you table:
1 name of field1
2 name of field2
3 tablename

The result will save in table TopRecord . parameter topNum is the number of records you want to show for each account, in your this case you should put 2.
====================================
private Sub TopRecord(topNum As Long)
Dim rs As DAO.Recordset
Dim tableName As String, field1 As String, field2 As String
Dim f1 As String, f2 As String
field1 = "Acct" 'field name 1
field2 = "[Date]" 'field name 2
tableName = "tablename"

' create empty table to hold top records
CurrentDb.Execute "delete from TopRecord"
CurrentDb.Execute "insert into TopRecord select DISTINCT " + field1 + ", " + field2 + " from " + tableName
Set rs = CurrentDb.OpenRecordset("select " + field1 + ", " + field2 + " from TopRecord order by " + field1 + ", " + field2 + " desc")
Do While Not rs.EOF
f1 = rs.Fields(0)
For i = 1 To topNum
rs.MoveNext
If rs.EOF Then Exit Do
If rs.Fields(0) <> f1 Then Exit For
Next
Do While rs.Fields(0) = f1
rs.Delete
rs.MoveNext
Loop
Loop
Set rs = Nothing
End Sub
========================================

Notice: The above code is for small tables. For large tables with millions of records and not so many accounts, the above code is ineffcicient because it copies all records into the new table then deletes.