Access 2000-Count based on values in another field 2

[mpadova]

I've tried a lot of places to find the answer to this problem and can't seem to get past it.

Without too much detail, I need a piece of code that would assign an incrementing number to each record based on the value of another field. So I need to turn:

ID[tab]ExportFileName[tab]Count
1[tab]A
2[tab]A
3[tab]B
4[tab]B
5[tab]B
6[tab]B
7[tab]C
8[tab]C
9[tab]C

Into:

ID[tab]ExportFileName[tab]Count
1 [tab]A [tab]1
2 [tab]A [tab]2
3 [tab]B [tab]1
4 [tab]B [tab]2
5 [tab]B [tab]3
6 [tab]B [tab]4
7 [tab]C [tab]1
8 [tab]C [tab]2
9 [tab]C [tab]3


Any help that anyone can give would be greatly appreciated.

 

[dhookom]

Try SQL something like:

SELECT ID, ExportFilename, 
   (SELECT Count(*)
    FROM tblNoName N
    WHERE N.ID <=tblNoName.ID AND 
         N.ExportFileName = tblNoName.ExportFileName) as TheCount
FROM tblNoName;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Another way:
SELECT A.ID, A.ExportFilename, Count(*) AS TheCount
FROM tblNoName A INNER JOIN tblNoName B ON A.ExportFilename = B.ExportFilename
WHERE A.ID >= B.ID
GROUP BY A.ID, A.ExportFilename

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mpadova]

Both of these work great. Thank you both!