Autonumber records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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..
 
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
 
Nai said:
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..
Click to expand...

Just a warning note before you get into trouble. It would appear you
are relying on the order in which the data appears in your table. Tables
don't really have an order. They tend to display data in an order, but next
week that may change. If you desire to have your data in a specific order
you must provide a means of setting that order yourself.

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.
 
Back
Top