MS-Access SQL Duplicate Entries on Join with if statement

I have an access DB and I am using the ‘DoCmd.RunSQL’ for various aspects of my DB to update/insert and delete from tables.

I have two tables ‘Table_1’ that has just over 8000 rows. I need to update specific rows in this table with a new balance from ‘Table_2’ which has about 5000 rows.

The common field in both tables are AccountID and SUBID.

If AccountID is null in Table_2 then I want to use SUBID as the join.

I have tried the following code:

Call RunSQL("INSERT INTO temp_Table " & _
    "SELECT TABLE_1.*  " & _
    ",TABLE_2.[Balance], TABLE_2.[T2_AccountID )], TABLE_2.[T2_SUBID )]   " & _
    "FROM (SELECT * FROM TABLE_1 WHERE TABLE_1.Business = 'Criteria1' AND TABLE_1.Type IN ('Criteria2','Criteria3') ) TABLE_1 " & _
    "LEFT JOIN TABLE_2 on " & _
    "( IIf(TABLE_2.[T2_AccountID )] IS NOT NULL, TABLE_2.[T2_AccountID )] = TABLE_1.[AccountID ] AND TABLE_2.[T2_SUBID )] = TABLE_1.[SUBID], TABLE_2.[T2_SUBID )] = TABLE_1.[SUBID]) ) ")

The problem with the above code is that it causes duplicates so increases my record set to over double the size of the original 8k.

The reason for this is that there are duplicate ‘SUBID’ values in both tables however not all these SUBID have a corresponding ‘AccountID’. So for items with an AccountID as null I want to use the SUBID as the join criteria, however for those that don’t have a null AccountID the join I specify does not seem to work and causes duplicates in these.

For example in table 1 for SUBID : 6583 there are two records one has a value in the AccountID field while the other is null.

In table 2 for the same SUBID of 6583 there are also 2 records, similar to TABLE1, one has a value in the AccountID field while the other is null.

So the join I am using in the code above is causing an addition record to be brought in meaning 3 records now exist instead of 2. Two of these records have a blank AccountID field from TABLE2, while 2 of the AccountID are populated from TABLE1, so it seems to be duplicating the TABLE1 record

I tried fixing this issue by using the following join criteria:

LEFT JOIN TABLE_2 on TABLE_2.[T2_AccountID )] = TABLE_1.[AccountID ] AND TABLE_2.[T2_SUBID )] = TABLE_1.[SUBID]

However when I use the above the records that have a null AccountID are not joining.

I’ve tried a few different ways and an’t get the two tables joining as I need to. Any help would be appreciated.

Thanks in advance,

Answer

Consider:

UPDATE Table1 INNER JOIN Table2 
ON Nz(Table1.AccountID,Table1.SubID)=Nz(Table2.AccountID,Table2.SubID) 
SET Table1.Balance = Table2.Balance
WHERE ...;

If you prefer not to use VBA Nz() function:
ON IIf(Table1.AccountID IS NULL, Table1.SubID, Table1.AccountID)=IIf(Table2.AccountID IS NULL, Table2.SubID, Table2.AccountID)

Advertisement

Answer