Number each occurrence of Multiple Criteria in a Access Query

[mikey3580]

I feel like there should be an easy way to do this and I am completely overlooking something

Have a query in access where by I want to use the "Transaction Code" and "Fund ID" and "Security Name" and number each occurence.

Example Line 1 is
Transaction Code - Buy
Fund ID - BLACK
Security Name - Apple
Number Each Occurrence - 1

Transaction Code Fund ID Security Name NUMBER EACH OCCURRENCE
Buy BLACK Apple 1
Buy BLACK Apple 2
Buy BLACK Apple 3
Sell BLACK Apple 1
Sell RED IBM 1
Buy BLACK Apple 4
Sell BLACK IBM 1
Sell RED IBM 2

I can't figure out how to get the number of occurences to work
Any help is greatly appreciated!

[XLent]

not sure if you're still looking into this, (would guess you have fixed), however, in general SQL terms I'd think of this as a ROW NUMBER & PARTITION type problem but, (to my very limited Access knowledge), this isn't available

if you have a source field that is unique/numeric, e.g. a Auto Number PK, you could use something like the below, where Field1 is the unique/numeric field

Please Login or Register  to view this content.