Make-table query and multiple autonumber fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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>
 
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
 
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
 
Worked perfectly! Thanks.

Jeff Boyce said:
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>

Tracey said:
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?
Click to expand...
Click to expand...
 
I am building a table to be used as a reference table for Excel users.
Click to expand...

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

John W. Vinson[MVP]
 
Thanks- that is a good option.

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

John W. Vinson[MVP]
Click to expand...
 
Back
Top