Hi all....I will just like to say thanx in advance for reviewing my msg....well guy i'm kindda in a jam right now....well as u all can see from the image 2 file I uploaded with msg, I got 2 indentical db which is db1 and db2 with the same table sturcture (indentical)....
The problem is would to merge the 2 table in the diference db into 1 table so my data in either one of the db is update....so can anybody help me with please...please
Do a little reading on what are called union select queries. You can use the MS Access query builder to do one, but you type it in by hand, rather than drag-and-drop. It's not hard. There's a reference to union select queries in the help.
Alternatively, you can create two queries: one showing only those records of one table that are not in the other table, and then another query that uses the first query to append the difference of the two to the table of your choice (or to make entirely new third table, if you want, containing the contents of both of the first tables).
But that's more work. Look into the union select query functionality first, and see if that gets you anywhere.
I got a simular problem but a little bit different. I got two tables with exactly the same fields (and proporties). I can merge them into 1 with the "union select"-query and all, but now i want to merge them into 1 table, BUT i want to keep the original autonumberfield as autonumbering (autonumbering in table1 = 0-4000) and autonumbering in table2 = 6000-8000, so there arent any identical autonumbers in the ID field). I do not want to loose the autonumber which goes with each record. And i dont want to add a new autonumbering field.
Anyone got any suggestions (if possible ofcourse).
A simple append query using the Access query tool will do the trick. Append each field, including the autonumber field, of one table to the other. You'll preserve the number sequencing without errors (assuming you truly have two distinct batches of sequenced autonumbers).
If both tables had an autonumber field of MyRecordId, then some additional fields, you could do this:
INSERT INTO Table1 ( MyRecordID, Field1, Field2, Field3 )
SELECT Table2.MyRecordID, Table2.Field1, Table2.Field2, Table2.Field3
FROM Table2;
worked perfectly.. Never tried it, because i thought the autonumbering of table 2 wouldnt be inserted into table 1. Next time I'll experiment some more..
Reply With Quote