Cannot have two autonumber fields

Question

I have a query that includes an autnumbered index from another table. This throws an error that says cannot have two autnumber fields in one result.

I used

AliasID: Numberformat([ForeignID],0)

in my query, and that works alright, except it comes back with the thousands separators in the ID numbers, which I don't want/

How do I get rid of those?

Answer 1

Right Val.  I knew that once upon a time.

Thanks

Answer 2

I assume that this is a 'make table' query.  You can convert the autonumber column to a straightforward number by means of the Val function.  Here's an example using a couple of my own tables:

SELECT Contacts.ContactID, Contacts.FirstName, Contacts.Lastname,

VALl(Cities.CityID) AS CityID, Cities.City, Cities.County

INTO NewTable

FROM Cities INNER JOIN Contacts

ON Cities.CityID = Contacts.CityID;

Note that a 'make table' query creates a column of double precision number data type for the CityID rather than a long integer number, which an autonumber is, so the data type of the column should be changed.  Better still, however, is not to use a 'make table' query at all but to insert the rows into a previously designed empty table.  There is then no need to call the Val function as the values from the autonumber column will successfully be inserted:

INSERT INTO NewTable ( ContactID, FirstName, Lastname, CityID, City, County )

SELECT Contacts.ContactID, Contacts.FirstName, Contacts.Lastname,

Cities.CityID, Cities.City, Cities.County

FROM Cities INNER JOIN Contacts

ON Cities.CityID = Contacts.CityID;

Answer 3

Try this --

AliasID: Val([ForeignID])