I have two tables. A temp table [tvwBundle] that accumulates information entered by the user and then a main table [tblBundle] that the temp table populates. I want the query to only list the differences between the two table and have only found ways to query the similarities.

Currently I am playing with different options I can find in the query designer but am intending to put this into vba, I have not attempted the vba code since I cannot get the query to work. If you want to go the extra mile, I would really appreciate what this would look like in vba as well.

I have tried:

SELECT tblBundle.BundleNbr & tblBundle.BundleLtr AS Bundle, tblBundle.Step
FROM tblBundle
WHERE tblBundle.WO="195687-1-1" AND tblBundle.Step=200
UNION
SELECT tvwBundle.BundleNbr & tvwBundle.BundleLtr as Bundle, tvwBundle.Step
FROM tvwBundle 
WHERE tvwBundle.WO="195687-1-1" AND tvwBundle.Step=200;

SELECT DISTINCT tblBundle.*
FROM tblBundle
INNER JOIN tvwBundle ON tvwBundle.BundleNbr = tblBundle.BundleNbr WHERE tblBundle.WO = 
"195687-1-1" AND tblBundle.Step = 200

SELECT DISTINCT tblBundle.*
FROM tblBundle
Left JOIN tvwBundle ON tvwBundle.BundleNbr = tblBundle.BundleNbr WHERE tblBundle.WO = "195687- 
1-1" 
AND tblBundle.Step = 200

SELECT DISTINCT tblBundle.*
FROM tblBundle
Left OUTER JOIN tvwBundle ON tvwBundle.BundleNbr = tblBundle.BundleNbr WHERE tblBundle.WO = 
"195687-1-1" AND tblBundle.Step = 200

The 'RIGHT JOIN' didn't do it either.

CodePudding user response：

MS Access appears to not support minus, except or even a full outer join, so I'd recommend you to use left/right outer joins and filter the results

let's assume I have 2 tables

tab_1

tab_2

So, the expected result would be

It might be achieved by using this query

select tab_1.text_col
  from tab_1 
  left join tab_2 
    on tab_1.text_col = tab_2.text_col
 where tab_2.text_col is null -- together with left join this condition will show you lines that exists in tab_1 only
 union all
 select tab_2.text_col
   from tab_1
  right join tab_2 
     on tab_1.text_col = tab_2.text_col
  where tab_1.text_col is null -- together with right join this condition will show you lines that exists in tab_2 only

CodePudding user response：

Use a merge statement. This is a typical basic ETL process (Extract, Transform, Load). Except in your case, you don't seem to be doing a lot of Transforming.

I assume you're using MSSQL. Also, you may want to research Slowly Changing Dimensions. The change you are looking for here is SCD1, where the change history is not preserved, and the matching entry is overwritten when changed. Things get fun when you want to preserve the change history (that's SCD2)

See:

MERGE (Transact-SQL) - Microsoft Docs

Basic sql-server-merge

You've asked for query, however the merge will write to your main table.

It would be something like this...

MERGE tblBundle target USING tvwBundle source
ON 

-- use a normal join condition here, where the matching keys exist. 
-- Looks like you've got a Work Order column 
-- I've completely guessed your keys from the info above
tblBundle.WO = tvwBundle.WO AND 
tblBundle.Step = tvwBundle.Step AND 
tvwBundle.BundleNbr = tblBundle.BundleNbr

WHEN MATCHED
    THEN 
-- Your update_statement here, updating the main table
-- Use the source and target aliases specified in the initial MERGE line

WHEN NOT MATCHED
    THEN 
-- Your insert_statement here 
-- Again, use the source and target aliases specified in the initial MERGE line


-- optionally, delete rows in target that don't exist in source, 
-- I don't think you want this. 
-- WHEN NOT MATCHED BY SOURCE
--     THEN DELETE;

The sqlserver central link above describes the actual merge statement well.