Get 1st , 2nd, 3rd etc..Occurence of a text or number

[punna111]

Hi all,

below is my data i have.

Amt
100
50
100
50
300
50
50

i need the count based on the occurrence, Count if is giving only total of count, but i need as below.

Amt	time of occurrence
100	1
50	1
100	2
50	2
300	1
50	3
50	4

Thanks,

[Perceptus]

Im confused by your post. Are you using an aggregate query with 2 fields? Like below?

SELECT Amt, Count(Amt) AS CountOfAmt
FROM tblNotSpecified
GROUP BY Amt

[punna111]

i used all the ways including above, but not getting the actual result

[Perceptus]

Can you attach the DB?

[punna111]

Here is the db.Database2.zip

[CJ_London]

as advised in your almost identical question here

https://www.accessforums.net/showthread.php?t=58083

this cannot be done without a uniqueID (typically an autonumber)