When you add criteria to an Outer Join, sometimes it's like not having the
outer join at all.

You may need to break this one big query down to seperate queries and then
string them together. This will allow for the criteria to be applied at the
individual query level, without affecting the join.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

"Tim" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> This simple query below with 'Select DISTINCT' shows me 1 contact,
> regardless of how many times a contact matches to any of the multiple
> fields and criteria in the WHERE. I can not show the fields from the
> Left Joined tables or I'll get multiple contacts. That's ok though.
>
> The problem I'm having is that I want to leave the criteria of the
> fields in the Left Joined tables blank so that I can specify it as
> part of a Filter String created in code attached to the On Open event
> of a report.
> If I leave the criteria blank, then these fields from the Left Joined
> tables don't stay as part of my query fields because they aren't
> shown, and have no criteria to make them useful. So when I run my
> report, it fails because it doesn't find the fields in the query.
>
> That's a lot harder to say than to see.
> Anyone have a solution to this? Seems like it should be simple.
>
> SELECT DISTINCT dbo_CustomerContact.Contact_Name
> FROM ((dbo_CustomerContact LEFT JOIN dbo_ContactSoftware ON
> dbo_CustomerContact.CustomerContact_ID =
> dbo_ContactSoftware.CustomerContact_ID) LEFT JOIN
> dbo_ContactSpecialList ON dbo_CustomerContact.CustomerContact_ID =
> dbo_ContactSpecialList.CustomerContact_ID) LEFT JOIN
> dbo_CustomerContactCategory ON dbo_CustomerContact.CustomerContact_ID
> = dbo_CustomerContactCategory.CustomerContact_ID
> WHERE (((dbo_CustomerContactCategory.Category_ID)=1 Or
> (dbo_CustomerContactCategory.Category_ID)=2 Or
> (dbo_CustomerContactCategory.Category_ID)=3) AND
> ((dbo_ContactSpecialList.List_ID)=1) AND
> ((dbo_ContactSoftware.Software_ID)=1 Or
> (dbo_ContactSoftware.Software_ID)=2));
>

Using two queries does at least save the fields in Query #1, because I
can show the fields in query #1, and then do a SELECT DISTINCT on
query #2 and not show them.
Problem still remains though that now I have to base my report on
Query #2, where the fields don't exist. I don't know how to
programmatically affect the criteria of query #1 when it is not used
as the reports control source.
Any thoughts on this?

On Wed, 16 Feb 2005 09:03:24 -0500, "[MVP] S.Clark"
<(E-Mail Removed)> wrote:

>When you add criteria to an Outer Join, sometimes it's like not having the
>outer join at all.
>
>You may need to break this one big query down to seperate queries and then
>string them together. This will allow for the criteria to be applied at the
>individual query level, without affecting the join.