Create table query... want to insert autonumber column

[DanEgg]

I have a create table query where I would like to add an addditional column
that is an autonumber column. How can I reference autonumber within the
SELECT to get it to populate successfully?

Here is the query in question:

SELECT A.COLUMN_A
,A.COLUMN_B
,A.COLUMN_C
,????? COLUMN_D
FROM
A
ORDER BY
A.COLUMN_A
,A.COLUMN_B

Is it possible to reference AUTONUMBER to populate COLUMN_D during the
create table query?

 

[Michel Walsh]

With Jet, you cannot do it directly. Sure, you can RANK your records among
themselves (who is first, who is second, ... based on values in the other
fields), with JOIN or with subquery, but that take time proportional to the
square of the number of records you have to rank (while ordering is much
faster).


Vanderghast, Access MVP

 

[Jeff Boyce]

They're called "autonumbers" for a reason ... if you are talking about the
Access Autonumber data type, you CAN'T! Access creates those automatically.

If you are talking about something else, please describe further...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have a create table query where I would like to add an addditional column
that is an autonumber column. How can I reference autonumber within the
SELECT to get it to populate successfully?

You can't.

But... one big question... why do you need a MakeTable query AT ALL? They're
*very* rarely necessary in a properly designed application; they cause rapid
bloating of the database; they don't give you control over field sizes, or
formats, or autonumbers, or lots of other things.

What is the purpose of this new table, and can it not be attained by using a
proper SELECT query?