Re: How to left

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
'====================================================


Dalt wrote:
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 ON ChangeRecords.ChangeRecord=Assignments.RecordNumber
GROUP BY ChangeRecords.ChangeRecord;

"Brian" wrote:

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" wrote:

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" wrote:

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" wrote:

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!
.

Relevant Pages