If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Last record/entry from a table
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. |
#2
|
|||
|
|||
Last record/entry from a table
Hurrikane4 wrote:
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. 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 -- HTH, Bob Barrows |
#3
|
|||
|
|||
Last record/entry from a table
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 -- Build a little, test a little. "Hurrikane4" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|