Combine 4 Tables into 1 Table??

[natemclain]

Hey I have a question on something that I have never done yet. I have 4 tables (memo51, memo52, memo53, memo54) they all have the same fields. The only duplicate records are the AutoNumber Fields. Here are the fields.
Field1 : AutoNumber
Field2 : Text
Field3 : Memo
Field4 : Memo
Field5 : Memo
Field6 : Memo
Field7 : Memo
Field8 : Memo

Ok I could even remove the AutoNumber field if need be to combine these tables into 1 table. And Add it back when all the tables are combined. Can anyone explain this step by step on how to do this?? It would save me hours of screwing up........

TIA

natemclain

 

[pcdaveh]

Determine which table you want to add the records too. If you want you can create a brand new table. The type of query that you want to run is an append query. Append writes records to the bottom of an existing table. If you want to create a brand new table you can create that table by creating a Make-Table query first. Then any other subsequent queries would be append queries. Let me know if you have any further questions.

 

[loonpants]

Or you could use a union query to do it if the fields are definitely the same and base whatever you want on the query...

SELECT * FROM MEMO51
UNION
SELECT * FROM MEMO52
UNION
SELECT * FROM MEMO53
UNION
SELECT * FROM MEMO54

Only problem with that is the resulting recordset is not updateable.

 

[loonpants]

Infact you could base a make table query on that and save faffing about with loads of append queries.

 

[natemclain]

Well I figured out how to do it with a Append Query... Worked great. Now Im importing more text files in and having a heck of a time getting them all in. This is time consuming.

natemclain

 

[scking]

And you could simply copy-and-paste if you don't feel like writing a query. Just make sure the field order is the same, select all columns EXCEPT the autonumber column to copy and then, on the target table, again select all columns EXCEPT the autonumber column and paste. The autonumber field will then create autonumbers appropriate for the new records.

This doesn't leave behind any temporary queries and is sooo simple.

Steve King Growth follows a healthy professional curiosity