update query copy source to destination table access 2010

Question

just trying to figure out how to do the following:

I have a 2 tables (tbl_WBint: destination and temp_Agtpinwb : source) and need to use update query to copy over fields in tbl_WBint. The issue is initially I used a query to initialize all columns in tbl_WBint with 0 before applying update query. After I use the below SQL update query it replaces some rows with fields from temp_Agtpinwb table, which is what I am looking to do but the other rows that had zero during initialize query are blanks. Is there a way to only run update query if the source table has entries that need to be updated to the destination table while keeping other values in destination table as 0 still?

Hope that makes sense.

UPDATE tbl_WBint 
INNER JOIN temp_Agtpinwb 
    ON tbl_WBint.[Agent Pin] = temp_Agtpinwb.[Agent Pin]
SET 
    tbl_WBint.[Agent Pin] = [temp_Agtpinwb]![Agent Pin], 
    tbl_WBint.[WB Current] = [temp_Agtpinwb]![WB Current], 
    tbl_WBint.[WB Prior Year] = [temp_Agtpinwb]![WB Prior Year],
    tbl_WBint.[WB Prior Year 3] = [temp_Agtpinwb]![WB Prior Year 3],
    tbl_WBint.[WB Jan Count] = [temp_Agtpinwb]![WB Jan Count], 
    tbl_WBint.[WB Feb Count] = [temp_Agtpinwb]![WB Feb Count],
    tbl_WBint.[WB Mar Count] = [temp_Agtpinwb]![WB Mar Count], 
    tbl_WBint.[WB Apr Count] = [temp_Agtpinwb]![WB Apr Count], 
    tbl_WBint.[WB May Count] = [temp_Agtpinwb]![WB May Count],
    tbl_WBint.[WB Jun Count] = [temp_Agtpinwb]![WB Jun Count], 
    tbl_WBint.[WB Jul Count] = [temp_Agtpinwb]![WB Jul Count], 
    tbl_WBint.[WB Aug Count] = [temp_Agtpinwb]![WB Aug Count], 
    tbl_WBint.[WB Sep Count] = [temp_Agtpinwb]![WB Sep Count],
    tbl_WBint.[WB Oct Count] = [temp_Agtpinwb]![WB Oct Count],
    tbl_WBint.[WB Nov Count] = [temp_Agtpinwb]![WB Nov Count], 
    tbl_WBint.[WB Dec Count] = [temp_Agtpinwb]![WB Dec Count];

Thanks

Answer 1

It sounds like some of the values from temp_Agtpinwb are null values and are over-writing your 0'd values, you could use

UPDATE tbl_WBint 
INNER JOIN temp_Agtpinwb 
    ON tbl_WBint.[Agent Pin] = temp_Agtpinwb.[Agent Pin]
SET 
    tbl_WBint.[Agent Pin] = [temp_Agtpinwb]![Agent Pin], 
    tbl_WBint.[WB Current] = Nz([temp_Agtpinwb]![WB Current],0), 
    tbl_WBint.[WB Prior Year] = Nz([temp_Agtpinwb]![WB Prior Year],0),
    tbl_WBint.[WB Prior Year 3] = Nz([temp_Agtpinwb]![WB Prior Year 3],0),
    tbl_WBint.[WB Jan Count] = Nz([temp_Agtpinwb]![WB Jan Count],0), 
    tbl_WBint.[WB Feb Count] = Nz([temp_Agtpinwb]![WB Feb Count],0),
    tbl_WBint.[WB Mar Count] = Nz([temp_Agtpinwb]![WB Mar Count],0), 
    tbl_WBint.[WB Apr Count] = Nz([temp_Agtpinwb]![WB Apr Count],0), 
    tbl_WBint.[WB May Count] = Nz([temp_Agtpinwb]![WB May Count],0),
    tbl_WBint.[WB Jun Count] = Nz([temp_Agtpinwb]![WB Jun Count],0), 
    tbl_WBint.[WB Jul Count] = Nz([temp_Agtpinwb]![WB Jul Count],0), 
    tbl_WBint.[WB Aug Count] = Nz([temp_Agtpinwb]![WB Aug Count],0), 
    tbl_WBint.[WB Sep Count] = Nz([temp_Agtpinwb]![WB Sep Count],0),
    tbl_WBint.[WB Oct Count] = Nz([temp_Agtpinwb]![WB Oct Count],0),
    tbl_WBint.[WB Nov Count] = Nz([temp_Agtpinwb]![WB Nov Count],0), 
    tbl_WBint.[WB Dec Count] = Nz([temp_Agtpinwb]![WB Dec Count],0);

NZ will replace your null values with zeroes. If this is the case, I believe you wouldn't need the "initializing query" either.

Answer 2

Simply use conditional logic with IIF() to check for NULL and either keep original value or update with source value. And consider using table aliases as shown below to cut down on long table names.

UPDATE tbl_WBint w
INNER JOIN temp_Agtpinwb a
    ON w.[Agent Pin] = a.[Agent Pin]
SET 
    w.[Agent Pin] = IIF(a.[Agent Pin] IS NULL, w.[Agent Pin], a.[Agent Pin]), 
    w.[WB Current] = IIF(a.[WB Current] IS NULL, w.[WB Current], a.[WB Current]), 
    w.[WB Prior Year] = IIF(a.[WB Prior Year] IS NULL, w.[WB Prior Year], a.[WB Prior Year]),
    ...