Unmatched Record Query

[lotharious]

I have a Query that gives me the original data

Item 1
Ordered 1
Total 1
Po # 1

And a Table that holds current data

Item
Est
Total
PO

I need to get unmatched data from the Query that is not in the table, and put that info in a seperate table for processing.

The fields match by
Item 1 = Item
Ordered 1 = Est
Total 1 = Total
PO # 1 = PO


The possibilities,
A new Item could be added.
A new PO could be Added, which will have multiple items.
The Ordered could be changed.

One possible solution I have come up with,
Make a temp table to hold the query data, then run 3 seperate queries off that table.
Is this the best way to go about it? Or is there something possibly better?

 

[Spenney]

If I understand what you are trying to achieve, create a new query with the Query and the Table as the sources.

Left Join each field in the query to the matching field in the table.

Add all of the fields in the original query to the output of the new query.

Add all of the fields of the table to the new query and give them criteria of "Is Null"

This will show all of the records in the original query with no matching record in the table.

 

[OhioSteve]

Okay, I have three questions for you:

1) Are there two tables, or more than two?

2) Assuming that there are just two tables, what uniquely identifies a record in each table? For example, in a list of employees each employee might have a unique employee ID. It might require two or more fields to identify a record uniquely. For example, if a company did not use employee number, then you might need first name + last name to identify someone uniquely.

3) Are the identifiers in both tables the same?

If you answer those questions we can probably help you.