I want to extract some records that are common to three tables, but not
contained in the fourth. Following what I have see in the archives and also
trying the unmatched records query wizard, if I only use one of those three
tables and use a LEFT JOIN to the fourth and set the target field in the
fourth table to null, I get the unmatched records.
However, if I link the three tables together and then want to exclude
records in the fourth table, it does not work.
What I have looks like this:
Table 1--->Table2--->Tabel3--->Table4 (is null)
Here is the code
SELECT DISTINCT Select_List_Performance.*, Select_List.Category
FROM ((Select_List_Performance LEFT JOIN Categories ON
Select_List_Performance.Morningstar_Category =
Categories.Morningstar_Category) LEFT JOIN Select_List ON
Categories.Morningstar_Category = Select_List.Category) LEFT JOIN
Sorted_Template ON Select_List.Ticker = Sorted_Template.Ticker
WHERE (((Select_List_Performance.Cat_Bnch) Like 'c*') AND
((Categories.Select_List_Grouping) Like 'Large*') AND
(((Sorted_Template.Ticker) Is Null)))
ORDER BY Select_List.Category;
If I were to split this up into two queries, one to create a new table with
the common records of the first three tables and the second to link this to
the fourth table to get unmatched records, it works. So I am guessing that
this might be a case of not using parentheses correctly and that a subquery
would work. But I've tried that without success. Any idea as to what I am
doing wrong?
Thanks
Alan
