Make-table query and multiple autonumber fields

[Guest]

I am building a table to be used as a reference table for Excel users. In my
make-table query, I have taken fields from 3 tables- the autonumber field
from each table and a text field from each table. When I try to run the
make-table query, I get the error message that only one autonumber field is
allowed. How do I get around this problem?

 

[Jeff Boyce]

Tracey

In your new "maked" table, do you actually even need an "Autonumber" field,
or do you just need the values that were created by Autonumber fields in
your three tables? If the latter, you can explicitly change the data type
of the three autonumbers to Long before you try to create the make table.

In your query, use something like:

CLng([YourAutonumberFieldNameFromTable1])

as a field.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Michel Walsh]

Hi,



The (only one) autonumber field value from TableA is stored as a LONG
integer, in tableB that refers to it. TableB can then refer to the (only
one) autonumber field from TableC and also from TableD, in other of it
fields, all of them LONG integer. In other words, use Autonumber in the
table where it is generated, use LONG integer where it is "referenced".



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I would say create a new table in design view with the three autonumber
columns and 2 text columns but just defined as NUMBER-LONG not autonumber.
Then create an UPDATE query to update this table.

Dorian

 

[Guest]

Worked perfectly! Thanks.

Tracey

In your new "maked" table, do you actually even need an "Autonumber" field,
or do you just need the values that were created by Autonumber fields in
your three tables? If the latter, you can explicitly change the data type
of the three autonumbers to Long before you try to create the make table.

In your query, use something like:

CLng([YourAutonumberFieldNameFromTable1])

as a field.

Regards

Jeff Boyce
<Office/Access MVP>

I am building a table to be used as a reference table for Excel users. In
my
make-table query, I have taken fields from 3 tables- the autonumber field
from each table and a text field from each table. When I try to run the
make-table query, I get the error message that only one autonumber field
is
allowed. How do I get around this problem?

 

[John Vinson]

I am building a table to be used as a reference table for Excel users.

Do note that Excel can link to a Select Query. It's not obligatory to
do the expensive MakeTable operation.

John W. Vinson[MVP]

 

[Guest]

Thanks- that is a good option.

Do note that Excel can link to a Select Query. It's not obligatory to
do the expensive MakeTable operation.

John W. Vinson[MVP]