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.
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).