Autonumber by groups

[Guest]

I am trying to create an autonumber field within a query, but I would like to
autonumber by groups. I have two fields in the query, but would like the
third to be the autonumber grouped on the values in the first field. My data
would look something like this if successfull:

1 IL 1
2 MO 1
2 IL 2
2 KS 3
2 IA 4
3 IL 1
3 MO 2

Can it be done?

Kev

 

[Jeff Boyce]

Kevin

With Access' Autonumbers, no. With "custom autonumbers", certainly! Check
mvps.org/access for "custom autonumbers" (which really means "custom
sequential numbering").

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tom Ellison]

Dear Kevin:

Autonumber is probably not the term you mean. Ranking is probably more
appropriate. An autonumber is a value permanently assigned and unique
within a table. A rank is derived from the data at the moment and does not
need to be unique.

Your data source is:

1 IL
2 MO
2 IL
2 KS
2 IA
3 IL
3 MO

Now, I can see these are ordered by the left hand column first, but then
what? What causes MO to come before IL within the group where the first
column is 2? Is there some other column that puts them in this order, but
which you did not show? Why does MO rank before IL, then KS, and finally
IA?

Without unique ordering, a query cannot rank these. I hope there is some
ordering so we can proceed.

Tom Ellison

 

[Guest]

Tom:

My problem does not require that I rank on anything within each group, but
it does not matter if I do anyway, as long as I start at #1 and number each
case using the first variable used for grouping. So if I would want to rank
my second variable, e.g., MO, IL, KS, starting a new rank with each new
number, how could this be done.

Thanks for the response.

Kev

 

[Guest]

Will check it out. Thanks a lot.

Kev

Kevin

With Access' Autonumbers, no. With "custom autonumbers", certainly! Check
mvps.org/access for "custom autonumbers" (which really means "custom
sequential numbering").

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tom Ellison]

Dear Kevin:

Unless you specify some unique ordering, this cannot be done with a query.
A query is a very "determinate" form of programming. If it is acceptable to
rank them alphabetically by state, then it is possible. Since you don't
have them in any particular order anyway, wouldn't this be acceptable:

SELECT Column1, Column2,
(SELECT COUNT(*)
FROM YourTable T1
WHERE T1.Column1 = T.Column1
AND T1.Column2 <= T.Column2)
AS Rank
FROM YourTable T
ORDER BY Column1, Column2

In the above, replace Column1 and Column2 with your column names, and
YourTable with the name of your table.

Tom Ellison

 

[Guest]

Tom:

After using your suggestion, the query prompts me for a value for T.Column1
and T.Column2. When I leave blank, the resulting output in the Rank field is
just the total number of records. Not sure why this is occurring.

Kev

 

[Guest]

Tom:
Reckecked and discovered I was missing a T in one of the expressions. Thanks
a lot. This will same a lot of manual work.

Kev