Autonumber records

[Nai]

I need to assign numbers to a set of records within a group as follow:

Original Table T
colA colB
john green
john yellow
john red
john blue
mary red
mary black
mary blue

Query:
colA colB(ascending) colC(auto number)
john blue 1
john green 2
john red 3
john yellow 4
mary black 1
mary blue 2
mary red 3

Please help, thanks..

 

[Jeff Boyce]

I don't understand the algorithm by which you decided which row gets which
number.

Note that in MS Access, "Autonumber" means a long integer
automatically-generated by Access.

Are you saying that you want to have a number assigned as part of a query?
If you assign "1" to two different rows, what does "1" mean (i.e., it isn't
unique)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tom Wickerath]

Hi Nai,

How about something like this?

SELECT T.colA, T.colB, (SELECT Count(C.colA)
FROM T AS C
WHERE C.colA=T.colA AND C.colB<=T.colB) AS colC
FROM T
ORDER BY T.colA, T.colB;

You can convert this into a Make Table query, if you want to create the new
column C as a permanent field.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________