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

[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.

 

[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,

 

[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.

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.

 

[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

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.

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

field

to

 

[Neil Robbins]

Hi Cheryl,

Problem solved. Thanks for the help - much appreciated.

Regards,

Neil R.

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

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.

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.

 

[djdave238]

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,

14 years later... Thank you Cheryl!