databasems-access

Get some records from one table then get records that don't match those from another table


I have 2 tables I need information from. "Jobs" and "tempItems".

Jobs table:

JobID Item QTY
0007 Test 1 1
0007 Test 2 1

tempItems:

Item QTY
Test 1 0
Test 2 0
Test 3 0
Test 4 0

I need results from the Jobs table AND the tempItems that isn't in the Jobs table

Item QTY (explanation)
Test 1 1 <-- from jobs table
Test 2 1 <-- from jobs table
Test 3 0 <-- from tempItems table
Test 4 0 <-- from tempItems table

I just can't seem to wrap my head around it.


Solution

  • As @Gustav said - the wizard will help you create an "Unmatched Query" to return everything in tempItems that isn't in Jobs:

    SELECT tempItems.Item, tempItems.Qty
    FROM tempItems LEFT JOIN Jobs ON tempItems.[Item] = Jobs.[Item]
    WHERE (((Jobs.Item) Is Null));
    

    Then all you need to do is Union that with a query returning everything from the Jobs table (and remove all the brackets - Access just loves brackets).

    SELECT  Item, 
            Qty
    FROM    Jobs
    
    UNION SELECT tempItems.Item, 
                 tempItems.Qty
    FROM tempItems LEFT JOIN Jobs ON tempItems.Item = Jobs.Item
    WHERE Jobs.Item Is Null
    

    There's no need to name the table for each field in the first query - Jobs is the only table being used.

    The field names in the second (unioned) table need the table name as Access won't know if you mean the field in tempItems or Jobs.

    I've removed all the brackets (square brackets are only need around fields with a space in).

    enter image description here