Numbering in a recordset or Query

[Addmore]

Hi All

I have a Query which returns records that look something like this:

Code QuantitySold Number
102 98 ?
102 110 ?
129 2 ?
129 78
129 67

Now in the recordset as you can see there are codes that can come up twice or three tmes, whatever the case.

Wat I would like to do is, in the Number Column I would like a line number to be generated, say in the case of Code 102, the first line would be 1 the second line would be 2, and for 129 the count would start again as 1, 2, 3...

So the result would look something like this

Code QuantitySold Number
102 98 1
102 110 2
129 2 1
129 78 2
129 67 3
405 25 1

Effectively Code 102 has two records hence it is numbered 1, 2
Code 129 has 3 occurances hence it is numbered 1, 2, 3 and so on.
I have more than 100 different codes that carry on like this in one recordset and so would like to have a query or function to automate this output. Am using Access 2002.
Thank you
Please Help!

 

[sconly]

Don't take this the wrong way but can you work with vba, recordsets and sql?

 

[gemma-the-husky]

its hard to get the sequence number in the query

easier but still awkward to get it in a form

easy to get it in a report

the reason being that a report is ALWAYS ordered, therefore does have a specific sequence, but tables and forms do not

 

[Addmore]

Yes I can work with vba, recordsets and sql, although I can say I am a beginner. Would like my function to be able to start a count everytime a new code appears as in the example.
Thanks