Problem with Make Table Query - Resultant Table not allowed to have more than one AutoNumber field.

N

Neil Robbins

I'm trying to write a MakeTable query that will bring together data from a
number of tables and export it as a single table to another database.
Unfortunately when I run this query I get the following error "Resultant
Table not allowed to have more than one AutoNumber field."

Does anyone know a way of changing the Data Type of an autonumber field to a
compatible data type within a make table query.

Any help is always appreciated.

Neil R.
 
C

Cheryl Fischer

You can export this second Autonumber as a Long. Try putting the following
in the first row of one of the columns in your query:

OldAutoNum: CLng([SecondAutoNum])


hth,
 
N

Neil Robbins

Hi Cheryl,

Thanks for the response, I have tried this but am getting an error of
"Extra ) in query expression 'TableName.[CLng([SecondAutoNum])]'

Have I done somthing wrong in implementing what you suggested?

Is there a way of writing this directly in SQL using the SQL view?

Thanks again,

Neil R.

Cheryl Fischer said:
You can export this second Autonumber as a Long. Try putting the following
in the first row of one of the columns in your query:

OldAutoNum: CLng([SecondAutoNum])


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Neil Robbins said:
I'm trying to write a MakeTable query that will bring together data from a
number of tables and export it as a single table to another database.
Unfortunately when I run this query I get the following error "Resultant
Table not allowed to have more than one AutoNumber field."

Does anyone know a way of changing the Data Type of an autonumber field
to
a
compatible data type within a make table query.

Any help is always appreciated.

Neil R.
 
C

Cheryl Fischer

Neil,

Try ...

CLng([TableName].[SecondAutoNum])

or, in SQL

CLng([TableName].[SecondAutoNum]) as OldAutoNum

You were inserting the function between the table name and the field name,
which must be treated as a whole.

hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Neil Robbins said:
Hi Cheryl,

Thanks for the response, I have tried this but am getting an error of
"Extra ) in query expression 'TableName.[CLng([SecondAutoNum])]'

Have I done somthing wrong in implementing what you suggested?

Is there a way of writing this directly in SQL using the SQL view?

Thanks again,

Neil R.

Cheryl Fischer said:
You can export this second Autonumber as a Long. Try putting the following
in the first row of one of the columns in your query:

OldAutoNum: CLng([SecondAutoNum])


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Neil Robbins said:
I'm trying to write a MakeTable query that will bring together data
from
field
 
N

Neil Robbins

Hi Cheryl,

Problem solved. Thanks for the help - much appreciated.

Regards,

Neil R.

Cheryl Fischer said:
Neil,

Try ...

CLng([TableName].[SecondAutoNum])

or, in SQL

CLng([TableName].[SecondAutoNum]) as OldAutoNum

You were inserting the function between the table name and the field name,
which must be treated as a whole.

hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Neil Robbins said:
Hi Cheryl,

Thanks for the response, I have tried this but am getting an error of
"Extra ) in query expression 'TableName.[CLng([SecondAutoNum])]'

Have I done somthing wrong in implementing what you suggested?

Is there a way of writing this directly in SQL using the SQL view?

Thanks again,

Neil R.

Cheryl Fischer said:
You can export this second Autonumber as a Long. Try putting the following
in the first row of one of the columns in your query:

OldAutoNum: CLng([SecondAutoNum])


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


I'm trying to write a MakeTable query that will bring together data
from
a
number of tables and export it as a single table to another database.
Unfortunately when I run this query I get the following error "Resultant
Table not allowed to have more than one AutoNumber field."

Does anyone know a way of changing the Data Type of an autonumber
field
to
a
compatible data type within a make table query.

Any help is always appreciated.

Neil R.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top