Autonumber by groups

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kevin - 28 Apr 2006 20:56 GMT

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

Reply to this Message

Jeff Boyce - 28 Apr 2006 21:13 GMT

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

>I am trying to create an autonumber field within a query, but I would like
>to
[quoted text clipped - 14 lines]
>
> Kev

Reply to this Message

Kevin - 28 Apr 2006 22:55 GMT

Will check it out. Thanks a lot.

Kev

> Kevin
>
[quoted text clipped - 25 lines]
> >
> > Kev

Reply to this Message

Tom Ellison - 28 Apr 2006 21:19 GMT

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

>I am trying to create an autonumber field within a query, but I would like
>to
[quoted text clipped - 14 lines]
>
> Kev

Reply to this Message

Kevin - 28 Apr 2006 22:51 GMT

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

> Dear Kevin:
>
[quoted text clipped - 42 lines]
> >
> > Kev

Reply to this Message

Tom Ellison - 28 Apr 2006 23:00 GMT

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

> Tom:
>
[quoted text clipped - 60 lines]
>> >
>> > Kev

Reply to this Message

Kevin - 29 Apr 2006 18:02 GMT

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

> Dear Kevin:
>
[quoted text clipped - 81 lines]
> >> >
> >> > Kev

Reply to this Message

Kevin - 29 Apr 2006 18:04 GMT

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

> Dear Kevin:
>
[quoted text clipped - 81 lines]
> >> >
> >> > Kev

Reply to this Message