Multiple autonumbers in a make table query

[aussieolie]

I am trying to make a table using a make table query. The fields that are required in the the new table are from multiple tables, however because the other tables all have autonumbers I cannot have more than one autonumber. What can I do cause i need both ID numbers from the seperate tables. However these autonumber dont need to generate a new ID in the table that will be automatically created from the query. So how do I put these autonumbers in my new table?

Thanks

 

[David R]

Errrr...huh? Let me see if this helps:
You can send your old autonumbers into straight numerical fields in your new tables.
You can leave off the autonumber field at first when you make the table and then add an autonumber to it. OR create the table beforehand and simply use an Append query to get your data into it, leaving the autonumber field to fill itself in automagically.

Hope that helps, post back if you're still confused.
David R

 

[aussieolie]

Thanks. I will now attempt to see if your second suggestion works.
Thanks very much.

Olie

 

[aussieolie]

I also like you first suggestion, can you please explain how I put autonumbers into normal number fields in the table that is going to be created from the make table query.

Thanks

Olie

 

[aussieolie]

I cannot define the fields as they are coming from a query, this query creates the new table which gets its fields from two other tables, which both have autonumbers (ID's) which I want to put into the one table created by the make table query.

Thanks

Olie

 

[RV]

I don't think you fully understood David's earlier message.

I quote:

>You can leave off the autonumber field at first when you make the table and then add an autonumber to it.<

After the make table query has run and created
a new table, open this new table in design view an adjust whatever autonumber field you want to.
Opposite to real DDL, you can't "include" the datatype of the columns in a new table created by a make table query (you're only making a copy, "make table query" don't exist...).

RV

 

[David R]

Define the table ahead of time and use an Append Query instead of a Make-Table Query. I've never used the latter but there probably is a way to define a field's type programmatically.

We should probably ask for the record here if it's really necessary that you make a new table with this information. Will a Select query not suffice anytime you need it? You are running the risk of breaking database normalization rules unless this data is coming from somewhere else you won't always have access to (no pun intended).

Good luck,
David R