RE: Aggregates, Joins, I am totally stuck on this issue

Don't get me wrong here.
You can use criteria tests on the field that is part of the join, as in...

SELECT tblIdentity.*
FROM tblMember LEFT JOIN tblMember ON tblIdentity.ID = tblMember.ID
WHERE tblMember.ID Is Null;

This will correctly return all Identities who are not members
But if for example you try

SELECT tblIdentity.*
FROM tblIdentity LEFT JOIN tblMember ON tblIdentity.ID = tblMember.ID
WHERE tblMember.StartDate <#Jan 01 2004#;

This will treat the left join as an inner join unless you add
WHERE tblMember.StartDate <#Jan 01 2004# or tblMember.StartDate is null;

If you have multiple criteria it quickly gets very messy, and it might pay
to create a "prefiltered" query to use in place of the table.

Sorry for the confusion

"ken" wrote:

> That is weird.. you mean if you reference the field from the "right" table in
> a left outer join it makes it an inner join? That would make access'
> solution for finding non-matching records between two table not work. Ya
> know, you tell it to find the records in one table that arent in another, so
> it does a join and checks that the id field from the "right" table equals
> null...
>
> Thanks for the hint
>

.