Merging tables without adding new records

[PHILnTEX]

A little help please.

In Access I have two tables (1 and 2), and the task is to insert tbl_2 into tbl_1.
• No new records should be created in tbl_1.
• tbl_1 and tbl_2 have a common field.
• The number of fields in both tables are known and will not change.
• tble_1 has fields that tbl_2 does not have.
• tbl_2 has some fields that tbl_1 does not have. These should be ignored.
• Creating a new tbl_3 instead of directly replacing tbl_1 is acceptable.

Example
The matching field in “b”
Table 1:
key a b c
--- ----------- ---------- -------
k0 hello dear world
k1 bye cruel world
k2 welcome outta world
k3 turulu
k4 welcome outta
k5 turulu dear

Table 2:
key b d
--- ------- -------
K0 dear john
K1 cruel ann

Table 3 (the new tbl_1):
key a b c d
--- ----------- ------- ------- -------
k0 hello dear world john
k1 bye cruel world ann
k2 welcome outta world
k3 turulu
k4 welcome outta
k5 turulu dear john

I'm new to Access and I must be missing something because I end up with duplicate records. Any help would be greatly appreciated.

 

[CJ_London]

in your query you should have a left join between table 1 and table 2

 

[PHILnTEX]

Hey CJ_London,

I’m super embarrassed that I didn’t actually test the sample database above. And yes, it most certainly does work as you stated. But it’s not working on my real-life database.

I’ve striped the two real-life tables down to KeyID and one text field each. The text fields are equivalent to field “B” in the above sample. Table_1 has 345,956 records and table_2 has 42,953 records. Most all the records in table_2 should matchup with records in table_1. But, when I left-join them I end up with 586,956 records.

It’s not a data type mismatch. What could cause this?

 

[CJ_London]

I don't know without seeing the data, but sounds like your key field in both tables are not unique i.e. if key k0 appears 20 times in table1 and 15 times in table 2, when joined this will create 300 records.

use the find duplicates wizard to find if you have duplicates.

 

[PHILnTEX]

CJ_London
I'm just now able to get back to this project. Indeed there were dups in table_2 field "b" caused by another field, lets call it "e" that had multiple values for some items in "b". Once cleaned up there were no more dups.
Thanks for pointing me in the right direction!