Need help on merging seven tables with the same layout, but different information from different groups into one main table- Please advice
Need help on merging seven tables with the same layout, but different information from different groups into one main table- Please advice
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?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.
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
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.Code:tblAAAA Field1 Field2 Field3 aaaaaa 111111 a1a1a1 bbbbbb 222222 b2b2b2 tblBBBB Field1 Field2 Field3 cccccc 333333 c3c3c3 dddddd 444444 c4c4c4
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
Sorry on my use of jargons...
I will try to figure it out with you suggestions.
Thanks
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;
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Thank you all- I was able to figure it out with the first posting