Creating a new table from mulitple tables with no key

[SeeWard]

Hello,
Is it possible to create a table from multiple tables if there is no key field? And if a key 'would' exist how would one go about creating the new table with only one autonumber field? I've tried to do this using a query and it will give an error saying that the resulting table can only have 1 autonumber field.

 

[dhookom]

You can create a make table query from more than one table where each has an autonumber. You can wrap the autonumber in a function like clng([YourField])

If you provided more information on what you are attempting to do and why, someone might be able to provide a less vague answer.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SeeWard]

Thanks Duane,
My dilema is as follows;
I have several tables that contain simliar information, the only difference in them is their region. I would like to combine all these tables together in ohe table and then just query that single table for the region needed. It's silly to have that many tables when they could be combined. Please note that the only key field in these tables is a system generated autonum field. When I tried to combine all the tables in that fashion, it errored out stating that I only can have 1 autonum field. What can I do? Thank you so much!

 

[PHV]

And what about an Union query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

If you use the UNION query as mentioned by PH, you may need to add a number to your primary key field from some of your tables to avoid duplicate autonumber values.

Do you have relationships set up to other tables that depend on this autonumber field in your tables?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]