RE: Aggregates, Joins, I am totally stuck on this issue
- From: ChrisJ <ChrisJ@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 10 May 2005 14:25:27 -0700
Don't get me wrong here.
You can use criteria tests on the field that is part of the join, as in...
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
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
> 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
> Thanks for the hint