How to create an Autonumber field in an Access query?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul - 24 Oct 2005 21:52 GMT

I need to create an Autonumber firld in a query. The query is based on two
tables and primary key (autonumber) is existed on both tables. I do not want
the primary key from neither of the table and I want to create a new
Autonumber ID field starting from 1 for the resultset of this query. This
query will then be used for a make-table query and this new Autonumber field
will be used to form part of the content on another field and the Autonumber
field itself will become the primary key of this new table. Thanks.

Reply to this Message

Douglas J. Steele - 24 Oct 2005 22:10 GMT

There's no way that I'm aware of to do this in a query.

You could create the table, with an Autonumber field in it, first, then
insert your data into it. As long as you leave the Autonumber field out of
the list of fields being inserted into, Access will generate the numbers for
you.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I need to create an Autonumber firld in a query. The query is based on two
>tables and primary key (autonumber) is existed on both tables. I do not
[quoted text clipped - 4 lines]
>Autonumber field itself will become the primary key of this new table.
>Thanks.

Reply to this Message

Al Camp - 24 Oct 2005 22:17 GMT

Paul,
Not sure why you're doing all that, but...
Rather than just a normal query, try an Append query, where all the field
definitions are waiting in an empty table... including the AutoNumber field.
You would delete all the records, compact the database to reset the
Autonumber counter in that table, and then Append/Populate the table with
the records from your query.
Should fly...

Signature

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Reply to this Message