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.
Orders table:
OrderNumber | CustomerID | BalanceDue |
---|---|---|
1000 | 1 | 250.00 |
1001 | 2 | 100.00 |
1002 | 2 | 50.00 |
1003 | 3 | 100.00 |
1004 | 1 | 200.00 |
Customers table:
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?
CustomerID
values in yourOrders
table that have no corresponding record in theCustomers
table? I'm not sure if this syntax is Access friendly,select * from Orders where CustomerID not in(select CustomerID from Customers)
.. would give orphaned orders.