0

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?

  • Do you have CustomerID values in your Orders table that have no corresponding record in the Customers 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. – Dan Dec 31 '15 at 21:03
  • I hadn't thought of that, it's definitely possible. I just checked the Customers table and there are definitely a lot of missing IDs that must have been deleted at some point. I'm going to look through the tables to be sure. If that's the issue then I'll need to rephrase the question. – Matt Jan 1 '16 at 7:45
  • Just for the record, though, your query so far is correct. And that means that whatever this post will ultimately end up asking about, it's likely to be different from what is being asked now. So, perhaps a new question would be a better idea? And I also think that @Dan has actually helped you to find the answer to this one (so may be one of you should post an answer). – Andriy M Jan 1 '16 at 10:56
  • @Dan I used your query to get a list of orphaned orders. It gave me 31 orders, but all had the same customer ID of '0'. So that would only account for 1 "missing" row from the GROUP BY query. For the record, my first query from the above question had 124 results, and my second had 146, so there's a difference of 22 customers. Your query only gave me a single customer ID that is missing so that leaves 21 customers that are missing in the first query, right? – Matt Jan 1 '16 at 18:53
  • Ah, I think I found the culprit. There are a lot of records in the Customers table that don't have any data in the 'Company' field, and in my first query there was only one row with nothing in the 'Company' field, so it is grouping all the customers with no 'Company'. What I need to accomplish is to group by the 'CustomerID', and then "replace" the 'CustomerID' with the 'Company' field that matches. Is this possible? – Matt Jan 1 '16 at 19:00
0

It also depends on what the end result needs to be.

If you want "Balance due per Customer", then you have the right query.

If you want "How much money do we still have to collect overall", then you need to also grab the money where there are no CustomerID, or where the CustomerID is not in the Company table. (Theses can be over-the-counter customers in a small store, for example).

So, try this: add a new row in Orders

INSERT INTO Orders (OrderNumber, CustomerID, BalanceDue) VALUES (1005, 5,999)

Now change your "INNER JOIN" for a "LEFT JOIN". This will get you all the values of the left table, even if there is no corresponding record in the right table.

SELECT Customers.Company, Sum(Orders.BalanceDue) AS Total
FROM Orders
LEFT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.Company;

You will see that you have 999$ assigned to the Customer "NULL". But at least you have all the money listed.

As with alot of things SQL and Reporting: it's all a matter of knowing exactly what you are looking for. Keep exploring, and keep asking questions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.