Insert a Sequence Number in Query

[am465]

I would like to insert a sequence number into a query that currently returns 7367 rows.

Here's what it looks like now:

ID_NUM	DTE_FIN_TRANS_RCV
1322	9/27/2012
1322	7/10/2012
1011	7/2/1999
1011	9/22/1999
1011	10/21/1999

Here's the desired outcome with sequences:

ID_NUM	DTE_FIN_TRANS_RCV	SEQUENCE
1322	9/27/2012	1
1322	7/10/2012	2
1011	7/2/1999	1
1011	9/22/1999	2
1011	10/21/1999	3

All I need is a way to number rows with a value higher than the previous for that particular ID_NUM. Any assistance with achieving this will be most appreciated. Thank you.

[lfpm062010]

Here is one way to do it.

SELECT MyTable.ID_NUM, MyTable.DTE_FIN_TRANS_RCV, DCount(ID_NUM,"MyTable","ID_NUM=" & MyTable.[ID_NUM] & " AND MyTable.DTE_FIN_TRANS_RCV <= #" & MyTable.DTE_FIN_TRANS_RCV & "#") AS SEQUENCE
FROM MyTable;

[June7]

What do you need that sequence number for?

Textbox in report has RunningSum property that can be used to accomplish the same.

[am465]

Thank you. This is working perfectly. I'm finding some cases where the sequence starts at 2 when duplicate rows exist. Is there a way to have it start at 1 in those cases? I tried grouping it to just display the last but that bogged it down. Here's an example of what it's doing:

Current Output

ID_NUM	DTE_FIN_TRANS_RCV	SEQUENCE
1650	9/17/2011	3
1650	6/05/2011	2
1650	6/05/2011	2

Desired Output

ID_NUM	DTE_FIN_TRANS_RCV	SEQUENCE
1650	9/17/2011	3
1650	6/05/2011	2
1650	6/05/2011	1

[June7]

Need another field that makes the records unique.

Review http://allenbrowne.com/ranking.html

Otherwise, use report.