How to INTERSECT tables for differences?

EDIT: added table descrptions.

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.

Both table have the same fields. WorkOrder, BundleNbr, BundleLtr, Clk, Shift, Step, (multiple) Dimensions, Complete, ActivityID

My form is designed to split Bundles into child bundles for various purposes. Once split, BundleLtr gets populated with our lettering scheme. I have a query: Me.cmbBundleToSplit.RowSource = "SELECT BundleNbr & BundleLtr AS Bundle FROM tblBundle GROUP BY BundleNbr & BundleLtr, [WO], [Complete], BundleNbr, BundleLtr HAVING [WO] = '" & Nz(Me.cmbShopOrder, "") & "' AND [Complete] <> False "ORDER BY BundleNbr, BundleLtr;"

For my WorkOrder there are 8 bundles that populate into the combobox: 1, 2, 3, 4, 5, 6, 7, 8. Once an order has been split, it gets held in the tempTable until the user is ready to post it.

In the case a user would split multiple bundles: Once Bundle 1 gets split, I would like the BundleNbr 1 removed from the rowsource. I do this via: result = fncMsgBox("Split another Bundle?", vbYesNo, "Split More?") If result = vbNo Then DoCmd.Close acForm, Me.Name End If

if the user clicks 'yes' I would like to reset the rowsource. So the RowSource would be: 2, 3, 4, 5, 6, 7, 8 Once Bundle 2 gets split the new RowSource would be: 3, 4, 5, 6, 7, 8 When the RowSource = "" I would have MsgBox "all Bundles are split"

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="WorkOrder" AND tblBundle.Step=200
UNION
SELECT tvwBundle.BundleNbr & tvwBundle.BundleLtr as Bundle, tvwBundle.Step
FROM tvwBundle 
WHERE tvwBundle.WO="WorkOrder" AND tvwBundle.Step=200;

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

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

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

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

EDIT: The goal is to use the results of the query as a RowSource for a combobox. After 1 bundle is split, it is posted to the tempTable; which collects all split bundles that data is being entered on.

If the BundleNbr&BundleLtr combo is in tempTable or has a > step in the main table, do not list the Bundle.

last step complete is 190 Entering data for step 200

tblBundle

tvwBundle

^^RowSource = 1, 2, 3, 4^^

While Splitting Bundles:

tvwBundle

tblBundle

^^RowSource = 2, 3, 4^^

While Splitting Bundle:

tvwBundle

tblBundle

^^RowSource = 3, 4^^

Then say the user doesn't get to the 2nd half until tomorrow. They will post the data entry so the main table looks like:

tblBundle

tvwBundle

^^RowSource = 3, 4^^

**I'm almost there with this:

intStep = 0
rst.Open "SELECT Step FROM tblBundle WHERE WO = '" & Me.cmbShopOrder & "' ORDER BY Step DESC;"

    If Not (rst.BOF And rst.EOF) Then
        rst.MoveFirst
        
        Do Until intStep <> 0
            If rst!Step >= Me.cmbStep Then
                rst.MoveNext
            Else
                intStep = rst!Step
            End If
        Loop
    End If
rst.Close
Me.cmbBundleToSplit.RowSource = "SELECT tblBundle.BundleNbr & 
tblBundle.BundleLtr as Bundle FROM tblBundle LEFT JOIN tvwBundle ON 
tblBundle.[BundleNbr] = tvwBundle.[BundleNbr] WHERE tblBundle.WO = '" & 
Me.cmbShopOrder & "' AND tblBundle.Step = " & intStep & " AND 
tvwBundle.BundleNbr Is Null;"

Solution 1:

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