Left Join Confusion

[Adam]

I thought I had this query finished, but apparently it's not working as I had
hoped...

I have two tables and one form. "Contacts" and "Calls" are the tables and "A
or M" is the form. I want to display all the records from "Contacts",
including all the associated calls from "Calls" where "ProductTypeV" (a field
in "A or m") is equal to iits dropdown value.

As the query stands below, the results are not narrowing to just the
"ProductTypeV" restriction and the field "call notes" doesn't display
properly. (Symbols are displaying.) Though, when I change it to a Right
Join the notes field shows up correctly, but the results remain unnarrowed by
the "
ProductTypeV."

Any ideas are more than appreciated.

-Adam

SELECT Contacts.[Wholesaler Name], Contacts.[Designated State],
Contacts.[Designated Area], Contacts.[Product Type], Calls.CallDate,
Calls.CallTime, Calls.Subject, Calls.Notes, Contacts.[Wholesaler ID],
Contacts.ContactID, Calls.CallDate, Contacts.City, Contacts.[Packet Sent to
Wholesaler Date], Contacts.[Payment for Allocation], Contacts.[Allocated Case
Volume], Contacts.[Employee]

FROM Contacts LEFT JOIN Calls ON Contacts.ContactID=Calls.ContactID

WHERE Contacts.[Product Type]=forms![A or M]!ProductTypeV And
Contacts.[Packet Sent to Wholesaler YN]=True And (Contacts.[Agreement
Received YN]=False Or Contacts.[Check Received YN]=False) Or (Contacts.[Open
Issue Resolved YN]=False And Contacts.[Open Issue YN]=True)
GROUP BY Calls.Notes, Contacts.[Wholesaler Name], Contacts.[Designated
State], Contacts.[Designated Area], Contacts.[Product Type], Calls.CallDate,
Calls.CallTime, Calls.Subject, Contacts.[Wholesaler ID], Contacts.ContactID,
Contacts.City, Contacts.[Packet Sent to Wholesaler Date], Contacts.[Payment
for Allocation], Contacts.[Allocated Case Volume], Contacts.[Employee];

 

[Ken Sheridan]

Adam:

You have an unnecessary GROUP BY clause in the query. As you are not
aggregating the values from any columns this is completely redundant and
conflicts with the outer join.

Ken Sheridan
Stafford, England

 

[Adam]

Thanks for the reply! I've deleted the Group by section (I wonder why Access
put that in?) and the notes section displays correctly. I'll keep trying to
figure out why it isn't narrowing the results by the form field correctly.

-Adam

Adam:

You have an unnecessary GROUP BY clause in the query. As you are not
aggregating the values from any columns this is completely redundant and
conflicts with the outer join.

Ken Sheridan
Stafford, England

I thought I had this query finished, but apparently it's not working as I had
hoped...

I have two tables and one form. "Contacts" and "Calls" are the tables and "A
or M" is the form. I want to display all the records from "Contacts",
including all the associated calls from "Calls" where "ProductTypeV" (a field
in "A or m") is equal to iits dropdown value.

As the query stands below, the results are not narrowing to just the
"ProductTypeV" restriction and the field "call notes" doesn't display
properly. (Symbols are displaying.) Though, when I change it to a Right
Join the notes field shows up correctly, but the results remain unnarrowed by
the "
ProductTypeV."

Any ideas are more than appreciated.

-Adam

SELECT Contacts.[Wholesaler Name], Contacts.[Designated State],
Contacts.[Designated Area], Contacts.[Product Type], Calls.CallDate,
Calls.CallTime, Calls.Subject, Calls.Notes, Contacts.[Wholesaler ID],
Contacts.ContactID, Calls.CallDate, Contacts.City, Contacts.[Packet Sent to
Wholesaler Date], Contacts.[Payment for Allocation], Contacts.[Allocated Case
Volume], Contacts.[Employee]

FROM Contacts LEFT JOIN Calls ON Contacts.ContactID=Calls.ContactID

WHERE Contacts.[Product Type]=forms![A or M]!ProductTypeV And
Contacts.[Packet Sent to Wholesaler YN]=True And (Contacts.[Agreement
Received YN]=False Or Contacts.[Check Received YN]=False) Or (Contacts.[Open
Issue Resolved YN]=False And Contacts.[Open Issue YN]=True)
GROUP BY Calls.Notes, Contacts.[Wholesaler Name], Contacts.[Designated
State], Contacts.[Designated Area], Contacts.[Product Type], Calls.CallDate,
Calls.CallTime, Calls.Subject, Contacts.[Wholesaler ID], Contacts.ContactID,
Contacts.City, Contacts.[Packet Sent to Wholesaler Date], Contacts.[Payment
for Allocation], Contacts.[Allocated Case Volume], Contacts.[Employee];

 

[Adam]

I figured it out. I had my "()'s" in the wrong place in the where sequence.

-Adam

Thanks for the reply! I've deleted the Group by section (I wonder why Access
put that in?) and the notes section displays correctly. I'll keep trying to
figure out why it isn't narrowing the results by the form field correctly.

-Adam

Adam:

You have an unnecessary GROUP BY clause in the query. As you are not
aggregating the values from any columns this is completely redundant and
conflicts with the outer join.

Ken Sheridan
Stafford, England

I thought I had this query finished, but apparently it's not working as I had
hoped...

I have two tables and one form. "Contacts" and "Calls" are the tables and "A
or M" is the form. I want to display all the records from "Contacts",
including all the associated calls from "Calls" where "ProductTypeV" (a field
in "A or m") is equal to iits dropdown value.

As the query stands below, the results are not narrowing to just the
"ProductTypeV" restriction and the field "call notes" doesn't display
properly. (Symbols are displaying.) Though, when I change it to a Right
Join the notes field shows up correctly, but the results remain unnarrowed by
the "
ProductTypeV."

Any ideas are more than appreciated.

-Adam

SELECT Contacts.[Wholesaler Name], Contacts.[Designated State],
Contacts.[Designated Area], Contacts.[Product Type], Calls.CallDate,
Calls.CallTime, Calls.Subject, Calls.Notes, Contacts.[Wholesaler ID],
Contacts.ContactID, Calls.CallDate, Contacts.City, Contacts.[Packet Sent to
Wholesaler Date], Contacts.[Payment for Allocation], Contacts.[Allocated Case
Volume], Contacts.[Employee]

FROM Contacts LEFT JOIN Calls ON Contacts.ContactID=Calls.ContactID

WHERE Contacts.[Product Type]=forms![A or M]!ProductTypeV And
Contacts.[Packet Sent to Wholesaler YN]=True And (Contacts.[Agreement
Received YN]=False Or Contacts.[Check Received YN]=False) Or (Contacts.[Open
Issue Resolved YN]=False And Contacts.[Open Issue YN]=True)
GROUP BY Calls.Notes, Contacts.[Wholesaler Name], Contacts.[Designated
State], Contacts.[Designated Area], Contacts.[Product Type], Calls.CallDate,
Calls.CallTime, Calls.Subject, Contacts.[Wholesaler ID], Contacts.ContactID,
Contacts.City, Contacts.[Packet Sent to Wholesaler Date], Contacts.[Payment
for Allocation], Contacts.[Allocated Case Volume], Contacts.[Employee];

 

[Ken Sheridan]

Adam:

The logic of the WHERE clause looks a bit suspect. Splitting it over
several lines makes it easier to follow:

WHERE
Contacts.[Product Type]=forms![A or M]!ProductTypeV
AND
Contacts.[Packet Sent to Wholesaler YN]=True
AND
(Contacts.[Agreement Received YN]=False
OR Contacts.[Check Received YN]=False)

OR

(Contacts.[Issue Resolved YN]=False
AND Contacts.[Open Issue YN]=True)

Because of the outer Boolean OR (the one I've isolated by inserting blank
lines before and after) a row will be returned if either of the two sets of
criteria are met, so in plain(ish) English this means if either:

1. The product type is the one selected in the form AND
2. The packet has been sent to the wholesaler AND
3. Either the agreement has been received OR the cheque received.

OR

1. The issue has been resolved OR its an open issue.

My guess would be that this last one is always going to be True as I'd
imagine an issue can only be either resolved or open, which would explain why
the query's result set is not being restricted.

This probably arises from entering these quite complex criteria in query
design view, where its necessary to use different lines of the grid to
represent a Boolean AND or OR. Looking at it in SQL view, particularly when
its set out over several lines (you can insert a line break anywhere in an
SQL statement BTW, it will work just the same), its much easier to analyse
the logic involved, so hopefully you'll be able to make the necessary
corrections in SQL view. Remember that to force an operation to evaluate
independently you enclose it in parentheses; this is most often done with OR
operations which you want to evaluate independently of ANDs.

The reason you had a GROUP BY clause in the query was almost certainly
because 'Totals' had been clicked on the View menu. This enables you to
build aggregating queries in design view, and by default groups by every
column until you change it to SUM, AVG etc for the relevant columns.

Ken Sheridan
Stafford, England