Last record/entry from a table

  • Thread starter Thread starter Hurrikane4
  • Start date Start date
H

Hurrikane4

My table has three columns, AcctNum, TransDate, SeqNum
I need a query that only displays the last "SeqNum" when an "AcctNum" had
multiple entries on the same day.

For example, my table has the following data:
AcctNum TransDate SeqNum
1234 6/2/10 698
1234 6/2/10 701
1234 6/2/10 705

In this case, I only want the see the one row with the highest "SeqNum"
(which is #705).

Thank you, in advance, for your help, I do appreciate it.
 
Hurrikane4 said:
My table has three columns, AcctNum, TransDate, SeqNum
I need a query that only displays the last "SeqNum" when an "AcctNum"
had multiple entries on the same day.

For example, my table has the following data:
AcctNum TransDate SeqNum
1234 6/2/10 698
1234 6/2/10 701
1234 6/2/10 705

In this case, I only want the see the one row with the highest
"SeqNum" (which is #705).

Thank you, in advance, for your help, I do appreciate it.
Click to expand...

If SeqNum is unique, then
select AcctNum,TransDate, ...
from yourtable as t join
(select AcctNum, TransDate,Max(SeqNum) As MaxSeqNum
FROM yourtable GROUP BY AcctNum, TransDate ) as q
ON t.AcctNum=q.AcctNum and t.TransDate=q.TransDate
and SeqNum = MaxSeqNum
 
Try this SQL --
SELECT AcctNum, TransDate, Max([SeqNum]) AS Last_SeqNum
FROM YourTable
GROUP BY AcctNum, TransDate;

Or in design view grid --
FIELD : AcctNum TransDate SeqNum
Table: YourTable YourTable YourTable
Total: Group By Group By Maximum
 
Back
Top