Query not working as expected

[BrianB via AccessMonster.com]

I have the following (sub)query (generated using the wizard), this is the
copied SQL code

SELECT Client.ID, Client.LastName, Client.FirstName, Client.Saltn, Client.
RegisterDate, Client.LastUpdate, Contact.ClientID, Contact.Category, Contact.
Primary, [contact]![type] & ": " & [contact]![contactdetail] AS Phone
FROM Client LEFT JOIN Contact ON Client.ID = Contact.ClientID
WHERE (((Contact.Category)="phone") AND ((Contact.Primary)=True));

This joins 2 tables, the client and their phone/email contacts (phone no.
email address) It allows for many categories (ex phone, email, website,..)
and types (home, mobile, business,...) and also indicates the one they wish
as primary. I wish to pick off the primary phone and email to include in
another query (because as it turns out it has this same problem, so if can
solve it here I won't need this solution, I'll be able to do it directly)
From this query I want EVERY client and, where there are phone numbers or
emails to have them ADDED to the result (the email part has been excluded
during this testing, but the same issue exists). The above SQL only returns
clients WHERE primary phone numbers exist. I set the join type in the
wizard for "all client" and "contacts where they exist" (A LEFT(outer) join
should be the right type as was generated in the SQL??).
My client DB has 169 records and the query returns 108 which if I count the
primary phone numbers is correct. When I add the emails I only get 97
records. I have tried the other join options and they don't work either.
Help please !!
thanks

 

[KARL DEWEY]

Try changing -- [contact]![type] & ": " & [contact]![contactdetail] AS Phone
to this ---
[contact].[type] & " : " & [contact].[contactdetail] AS Phone

 

[KenSheridan via AccessMonster.com]

You cannot restrict a query using a LEFT JOIN on a column in the table on the
right side of the join, as you are doing here on the Category and Primary
columns. In effect it turns it into an INNER JOIN.

It can be done by first creating a query on the Contact table restricted on
the Category and Primary columns:

SELECT ClientID, Category, Primary,
[type] & ": " & [contactdetail] AS Phone
FROM Contact
WHERE Category = "phone" AND Primary = TRUE;

Save this as qryPrimaryPhone say.

Then LEFT JOIN Client to this:

SELECT ID, LastName, FirstName, Saltn,
RegisterDate, LastUpdate, Category, Primary, Phone
FROM Client LEFT JOIN qryPrimaryPhone
ON Client.ID = qryPrimaryPhone.ClientID;

Ken Sheridan
Stafford, England

I have the following (sub)query (generated using the wizard), this is the
copied SQL code

SELECT Client.ID, Client.LastName, Client.FirstName, Client.Saltn, Client.
RegisterDate, Client.LastUpdate, Contact.ClientID, Contact.Category, Contact.
Primary, [contact]![type] & ": " & [contact]![contactdetail] AS Phone
FROM Client LEFT JOIN Contact ON Client.ID = Contact.ClientID
WHERE (((Contact.Category)="phone") AND ((Contact.Primary)=True));

This joins 2 tables, the client and their phone/email contacts (phone no.
email address) It allows for many categories (ex phone, email, website,..)
and types (home, mobile, business,...) and also indicates the one they wish
as primary. I wish to pick off the primary phone and email to include in
another query (because as it turns out it has this same problem, so if can
solve it here I won't need this solution, I'll be able to do it directly)
From this query I want EVERY client and, where there are phone numbers or
emails to have them ADDED to the result (the email part has been excluded
during this testing, but the same issue exists). The above SQL only returns
clients WHERE primary phone numbers exist. I set the join type in the
wizard for "all client" and "contacts where they exist" (A LEFT(outer) join
should be the right type as was generated in the SQL??).
My client DB has 169 records and the query returns 108 which if I count the
primary phone numbers is correct. When I add the emails I only get 97
records. I have tried the other join options and they don't work either.
Help please !!
thanks

 

[John W. Vinson]

I have the following (sub)query (generated using the wizard), this is the
copied SQL code

SELECT Client.ID, Client.LastName, Client.FirstName, Client.Saltn, Client.
RegisterDate, Client.LastUpdate, Contact.ClientID, Contact.Category, Contact.
Primary, [contact]![type] & ": " & [contact]![contactdetail] AS Phone
FROM Client LEFT JOIN Contact ON Client.ID = Contact.ClientID
WHERE (((Contact.Category)="phone") AND ((Contact.Primary)=True));

This joins 2 tables, the client and their phone/email contacts (phone no.
email address) It allows for many categories (ex phone, email, website,..)
and types (home, mobile, business,...) and also indicates the one they wish
as primary. I wish to pick off the primary phone and email to include in
another query (because as it turns out it has this same problem, so if can
solve it here I won't need this solution, I'll be able to do it directly)
From this query I want EVERY client and, where there are phone numbers or
emails to have them ADDED to the result (the email part has been excluded
during this testing, but the same issue exists). The above SQL only returns
clients WHERE primary phone numbers exist. I set the join type in the
wizard for "all client" and "contacts where they exist" (A LEFT(outer) join
should be the right type as was generated in the SQL??).
My client DB has 169 records and the query returns 108 which if I count the
primary phone numbers is correct. When I add the emails I only get 97
records. I have tried the other join options and they don't work either.
Help please !!
thanks

The problem is that as soon as you put a criterion on a table on the "outside"
of an outer join - the Contact table in this case - it's no longer really an
outer join: the Category and Primary fields are NULL, so the criterion rejects
the record.

One way to do this is to create a query PrimaryPhone:

SELECT Contact.ContactID, [contact].[type] & ": " & [contact].[contactdetail]
AS Phone FROM Contact WHERE Category = "Phone" and Primary = True;

Join *THIS* query to Client using a Left Join - it will show the primary phone
if there is one, and NULL if there isn't.

 

[BBC via AccessMonster.com]

Thanks Karl, Ken & John
Will give your ideas a try. I did try separating the Client & Contacts from
the Contribution but that gave me the same problem as per your comments on
the criterian's impact. Thanks to you guys I think I understand the problem
now.
By the way, when I respond to only the last post like this do all of you who
copntributed find out about it, I suspect so just like I see all of them (not
sure how you're going to answer if you don't)
thanks again.

I have the following (sub)query (generated using the wizard), this is the
copied SQL code

[quoted text clipped - 22 lines]

Help please !!
thanks

The problem is that as soon as you put a criterion on a table on the "outside"
of an outer join - the Contact table in this case - it's no longer really an
outer join: the Category and Primary fields are NULL, so the criterion rejects
the record.

One way to do this is to create a query PrimaryPhone:

SELECT Contact.ContactID, [contact].[type] & ": " & [contact].[contactdetail]
AS Phone FROM Contact WHERE Category = "Phone" and Primary = True;

Join *THIS* query to Client using a Left Join - it will show the primary phone
if there is one, and NULL if there isn't.

 

[John W. Vinson]

By the way, when I respond to only the last post like this do all of you who
copntributed find out about it, I suspect so just like I see all of them (not
sure how you're going to answer if you don't)

Yep, unless a post gets eaten by the internet gremlins, we'll see it whoever
it's directly addressed to; and that's fine, don't feel obligated to respond
multiple times.

 

[KenSheridan via AccessMonster.com]

Brian:

In my case any reply to a thread which I'm monitoring is notified to me. I
can't speak categorically for others of course, it will depend on the
interface they are using and whether they are still monitoring the thread,
but generally speaking people who've contributed to a thread should pick up
all responses.

BTW, if you are wondering why you sometimes get near identical replies from
different people its because people using different interfaces to a newsgroup
won't necessarily have yet seen another's reply, even if its earlier than
their own. Even in this electronic age not everything is instantaneous.

Ken Sheridan
Stafford, England

Thanks Karl, Ken & John
Will give your ideas a try. I did try separating the Client & Contacts from
the Contribution but that gave me the same problem as per your comments on
the criterian's impact. Thanks to you guys I think I understand the problem
now.
By the way, when I respond to only the last post like this do all of you who
copntributed find out about it, I suspect so just like I see all of them (not
sure how you're going to answer if you don't)
thanks again.
[quoted text clipped - 14 lines]

Join *THIS* query to Client using a Left Join - it will show the primary phone
if there is one, and NULL if there isn't.

 

[John W. Vinson]

BTW, if you are wondering why you sometimes get near identical replies from
different people its because people using different interfaces to a newsgroup
won't necessarily have yet seen another's reply, even if its earlier than
their own. Even in this electronic age not everything is instantaneous.

Yep... I use Agent, and usually in offline mode. I'll open the newsgroup,
download the new messages, and then (sometimes much later) actually read
through them and reply.