How to left

[Dalt]

Let's say I have 3 tables.

1) Customers
2) Orders
3) Complaints

Assume that Orders.FK and Complaints.FK are used to relate them to
Customers.PK.

What would the query look like to SELECT all CustomerID, CustomerName FROM
Customers and also count how many orders and how many complaints each
customer has?

Can anyone help? Thanks!

 

[Brian]

Do it graphically via the query builder.

Start a new query in design view.
Add all three tables.
Join the two FK's to the PK.
Drag CustomerID, OrderID, & ComplaintID to the query grid.
Click the Total button on the toolbar (Greek sigma - sideways "M")
Set the Total in the grid to this:

CustomerID: Group By
OrderID: Count
ComplaintID: Count

The SQL should look like this:

SELECT Customer.CustomerID, Count(Complaints.ComplaintID) AS ComplaintCount,
Count(Orders.OrderID) AS OrderCount
FROM (Customer INNER JOIN Complaints ON Customer.CustomerID =
Complaints.CustomerID) INNER JOIN Orders ON Customer.CustomerID =
Orders.CustomerID
GROUP BY Customer.CustomerID;

 

[Dalt]

This works, somewhat, however if there are 4 orders and 0 complaints, the
query correctly shows 4 orders and 0 complaints. If there is at least 1 of
both an order and a complaint however, it shows the highest of the Complaints
or Orders in BOTH fields.....

 

[Brian]

Post back here the entire SQL of your query as it exists.

Also, I neglected to deal with the possibility of 0 complaints/orders. You
will need to change the two joins to unequal joins. Right-click on the join
line and change them so they select all records from Customer & only those
matching records in the other table. That way, the customer will appear even
if there are no complaints or orders.

 

[Dalt]

Here's my exact query right now. Notice it's not exactly customers and
orders, but the table setup is exactly the same as my example. I can change
the Inner joins to a Left join and it will grab all records without any
issues or assignments, but it just doesn't compute the totals correctly for
both fields at the same time.

SELECT ChangeRecords.ChangeRecord, Count(IssueRecords.IssueRecord) AS
CountOfIssueRecord, Count(Assignments.Assignee) AS CountOfAssignee
FROM (ChangeRecords INNER JOIN IssueRecords ON
ChangeRecords.ChangeRecord=IssueRecords.ParentRecord) INNER JOIN Assignments

 

[John Spencer]

You are probably going to need to do this with subqueries. Also using
inner joins could eliminate records.

With your original tables and fields - I would use a query like the
following.

SELECT Customers.PK, AOrders.OrderCount, AComplaints.ComplaintCount
FROM (Customers
LEFT JOIN
(SELECT Customers.PK, Count(Orders.FK) as OrderCount
FROM Customers LEFT JOIN Orders
ON Customers.PK = Orders.FK
GROUP BY Customers.PK) as AOrders
ON Customers.PK = AOrders.PK)

LEFT JOIN
(SELECT Customers.PK, Count(Complaints.FK) as ComplaintCount
FROM Customers LEFT JOIN Complaints
ON Customers.PK = Complaints.FK
GROUP BY Customers.PK) as AComplaints
ON Customers.PK = AComplaints.PK

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================