Merge Several tables into one -Access 2010

[sylviar]

Need help on merging seven tables with the same layout, but different information from different groups into one main table- Please advice

[rpeare]

So the field layout is identical, you just need an identifier to tell you which table it originally came from?

If so just create an append query for each one of your tables but put an identifier in one of the fields (1 for table 1, 2 for table 2, for example) If there's no other identifying information in the tables themselves it just requires that you create a new table based on the design of the 'old' ones but add one field that will let you identify which table it originally came from.

[sylviar]

So the field layout is identical, you just need an identifier to tell you which table it originally came from?

If so just create an append query for each one of your tables but put an identifier in one of the fields (1 for table 1, 2 for table 2, for example) If there's no other identifying information in the tables themselves it just requires that you create a new table based on the design of the 'old' ones but add one field that will let you identify which table it originally came from.

Hi- I'm sorry ...I dont think I understand, so for me to merge all the data from seven tables..I have to create a quary for each table, but how do I put the identifier?

[rpeare]

Now you're confusing me with your use of jargon.

My understanding of your problem is this, you have multiple tables that all have identical fields/layout. You want to import all of those different tables into a single data source (table).

So let's assume you have these

tblAAAA
Field1  Field2  Field3
aaaaaa  111111  a1a1a1
bbbbbb  222222  b2b2b2

tblBBBB
Field1  Field2  Field3
cccccc 333333  c3c3c3
dddddd 444444  c4c4c4

now to get those into a single table you'd have to create an APPEND query for each EXISTING table to add it to your new 'main' table.

your new 'main' table would have to have the exact same fields as your original tables.

I added, purely because it's good practice, that you should add a primary key identifier (autonumber) to your new 'main' table AND possibly a secondary field to help you identify the source of the original data.

For instance let's say that tblAAAA is associated with salesperson AAAA, tblBBBB is associated with salesperson BBBB. You would want to know that (if the data itself couldn't) so you would add a field to your append query that was something like SOURCE: 'AAAA' and append it to a field called 'Source' in your new 'main' table

[sylviar]

Sorry on my use of jargons...
I will try to figure it out with you suggestions.
Thanks

[June7]

How much data is involved? Is this a one-time data fix? If each table has only few records, can copy/paste into the new table and then fill in the new field with the category or salesperson ID or whatever (AAAA, BBBB, etc).

Otherwise run INSERT SELECT sql Actions. Something like:

INSERT INTO tablename SELECT *, "AAAA" AS SalesID FROM tblAAA;

[sylviar]

Thank you all- I was able to figure it out with the first posting