How to Find Unmatched Data by Comparing 3 Quires with 1 Table

[RAJESHKUMAR R]

Dear Experts,

I have created three different Query Named as below

1. FirmOrder
2. 2. Forecast
3. 3. Shipment

And all these three contains common field that is Part_Number

Also, I have a Table Named as “FG_PN_LISTS” which is also contains a field as Part_Number

The Support which I am requesting here is,



I would like to compare all thee queries with “FG_PN_LISTS” Table and provide the single lists of Part_Number which is not available in “FG_PN_LISTS”!

Thanks & Regards,
Rajeshkumar R

[ranman256]

you want an OUTER join
join the Part list to the query on Part.
dbl-click the join line
set the property to : show ALL records from tParts, SOME from the query
bring down PART# from both into the grid
run query, the 'null' results will show what is missing.

[RAJESHKUMAR R]

Dear ranman256,


Thanks for your Response, I could able do above only for one Table with One Query.

However, I would like to compare all three Queries Part_Number Lists with FG_PN_LISTS and find Unmatched Data!

When I am trying I am getting an Error like "Ambiguous Join"

Thanks & Regards,
Rajeshkumar R

[isladogs]

This post may help with ambiguous join errors

https://www.accessforums.net/showthr...356#post390356

[Gicu]

The trick to do it all in one query is to properly create your outer joins. For that you need to start with your most inclusive set and go from there in a descending order. So maybe in your case Forecast->Shipment->FirmOrder->FG_PN_LISTS if the table is indeed the one with the least amount of part numbers.

Cheers,
Vlad