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

Discussion in 'Microsoft Access Queries' started by Neil Robbins, Apr 4, 2004.

  1. Neil Robbins

    Neil Robbins Guest

    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.
     
    Neil Robbins, Apr 4, 2004
    #1
    1. Advertisements

  2. 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, Apr 4, 2004
    #2
    1. Advertisements

  3. Neil Robbins

    Neil Robbins Guest

    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.

     
    Neil Robbins, Apr 4, 2004
    #3
  4. 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


     
    Cheryl Fischer, Apr 4, 2004
    #4
  5. Neil Robbins

    Neil Robbins Guest

    Hi Cheryl,

    Problem solved. Thanks for the help - much appreciated.

    Regards,

    Neil R.

     
    Neil Robbins, Apr 4, 2004
    #5
  6. Neil Robbins

    djdave238

    Joined:
    Dec 18, 2018
    Likes Received:
    0
    14 years later... Thank you Cheryl!
     
    djdave238, Dec 18, 2018
    #6
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.