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

Jobs table:

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

Jobs table:

tempItems:

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

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

• database
• ms-access

• 1 You can't having done much research on this. A left outer join between Item will do - Access even has a wizard that will help you to achieve this. – Gustav Commented Mar 31 at 11:24

2 Answers 2

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

Try construct query

SELECT iif(tempItems.Item is not null,tempItems.Item,Jobs.Item)  as [Item], 
   nz(nz(Jobs.QTY,tempItems.QTY),0)
FROM tempItems LEFT JOIN Jobs  ON tempItems.Item = Jobs.Item;