Make Table Query and Autonumber Fields

  • Thread starter Thread starter croy
  • Start date Start date
C

croy

In Access 2002, what triggers a MakeTable query to create a
field as an Autonumber field?
 
croy said:
In Access 2002, what triggers a MakeTable query to create a
field as an Autonumber field?
Click to expand...

Nothing. It can't be done. You can only create an Autonumber field in Design
View, or via a CREATE/ALTER TABLE DDL statement..

That means, if you need the resulting table to have an Autonumber field, you
have to create the table ahead of time and change the Make-table query to an
Append query.
 
Nothing. It can't be done. You can only create an Autonumber field in Design
View, or via a CREATE/ALTER TABLE DDL statement..

That means, if you need the resulting table to have an Autonumber field, you
have to create the table ahead of time and change the Make-table query to an
Append query.
Click to expand...

Actually, what I want is for a Make-table query to *not*
generate an Autonumber field.

My Make-table query is failing with the error, "You tried to
assign the Null value to a variable that is not a Variant
data type." There is one field in the query that seems to
want to be an Autonumber field, but I can't figure out why.

According to KB 197587, the query is failing because it is
creating an Autonumber field and then trying to put a null
in it. The KB only gives a workaround--build the table
first, avoiding Autonumber fields, and then use an append
query. That's how I got interested in the subject of this
thread.
 
croy said:
Actually, what I want is for a Make-table query to *not*
generate an Autonumber field.

My Make-table query is failing with the error, "You tried to
assign the Null value to a variable that is not a Variant
data type." There is one field in the query that seems to
want to be an Autonumber field, but I can't figure out why.

According to KB 197587, the query is failing because it is
creating an Autonumber field and then trying to put a null
in it. The KB only gives a workaround--build the table
first, avoiding Autonumber fields, and then use an append
query. That's how I got interested in the subject of this
thread.
Click to expand...

I guess either the behavior changed after A97 ... or my memory is failing
me, which is a distinct possiblity. I've just tried a test make-table query
using data from a table containing an autonumber field and, to my surprise,
the resulting table contained an autonumber field!

So it seems like the only way to avoid the autonumber is
1. not to include the autonumber field from the source in your select.
2. use the autonumber field in an expression so a new non-autonumber field
has to be created, like this:
select CLng(autonumberfield) as nonautonumber into newtable from oldtable
 
Nothing. It can't be done. You can only create an Autonumber field
in Design View, or via a CREATE/ALTER TABLE DDL statement..

That means, if you need the resulting table to have an Autonumber
field, you have to create the table ahead of time and change the
Make-table query to an Append query.
Click to expand...

That's just not true. When you use a MakeTable query and one of the
source fields is an Autonumber, the resulting table that is made has
an Autonumber type, as well.
 
David-W-Fenton said:
That's just not true. When you use a MakeTable query and one of the
source fields is an Autonumber, the resulting table that is made has
an Autonumber type, as well.
Click to expand...

So I found out, as you will see when you read my later reply. I don't know
where I got this wrong idea. Oh well, this is my something-new-learned for
today.
 
I don't know about triggers but by this way you can create incremental autonumbered field in your query

SELECT count(*) AS Ctr, Temp1.lname, Temp1.fname
FROM Table1 AS Temp1, Table1 AS Temp2
WHERE Temp1.lname>=Temp2.lname
GROUP BY Temp1.lname, Temp1.fname;
 
Back
Top