This seems like a really simple request, but I can't find the answer. I'll admit that I only have a very limited understanding of JOINs, so it could just be that I just don't fully understand the concept. I'm using a MS Access .mdb database, and running the query in MS Access 2010.
I need to SUM 'BalanceDue' and GROUP BY 'CustomerID', then I need to replace the 'CustomerID' with the 'Company' name from the Customers table.
OrderNumber CustomerID BalanceDue 1000 1 250.00 1001 2 100.00 1002 2 50.00 1003 3 100.00 1004 1 200.00
CustomerID Company 1 ABC Inc 2 XYZ Inc 3 Widgets LLC
And the expected result:
Company Total ABC Inc 450.00 XYZ Inc 150.00 Widgets LLC 100.00
Here's the query I came up with, but the results don't appear to be accurate:
SELECT Customers.Company, Sum(Orders.BalanceDue) AS Total FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID GROUP BY Customers.Company;
To check the accuracy, I just ran the following "simple" query (one I fully understand/trust):
SELECT Orders.CustomerID, Sum(Orders.BalanceDue) AS Total FROM Orders GROUP BY Orders.CustomerID;
When I compare the results from the two queries above, the results don't match. The first query has less rows, so it must be "skipping" some customers?
What am I doing wrong?