Issue involving sum within query returning null values, and then 0 with the addition of Nz() even though there is a quantity (MS Access)

Question

In MS Access, I have a query summing a quantities from one table, a union query summing hours from three different tables, and finally a third query joining these two queries through alike part numbers and part descriptions. When I do this, the summed hours for some of the part numbers comes back as null.

This is the SQL Code for the query that joins the two queries.

SELECT Quantity_Query.or_part_num, Quantity_Query.or_part_desc, Sum(Nz(Per_Hours_Query.per_hours,0)) AS Hours, Sum(Nz(Quantity_Query.ol_quantity,0)) AS SumOfol_quantity
FROM Per_Hours_Query RIGHT JOIN Quantity_Query ON (Per_Hours_Query.part_desc = Quantity_Query.or_part_desc) AND (Per_Hours_Query.part_num = Quantity_Query.or_part_num)
GROUP BY Quantity_Query.or_part_num, Quantity_Query.or_part_desc
HAVING (((Quantity_Query.or_part_num) Like Forms!Part_Search!PartNo_Search & "*") And ((Quantity_Query.or_part_desc) Like Forms!Part_Search!PartDesc_Search & "*"))
ORDER BY Quantity_Query.or_part_num;					

When I do the Nz() function, it comes back as 0 even though there are hours for most of the part numbers, while a very few come back with the correct amount. I need help as I am a beginner with MS Access, so any guidance would be appreciated.

this is an example of the incorrect results I receive with the above code

Answer 1

To start with, I don't know Access myself. But as you labelled the query SQL Server as well, I saw the query, as SQL Server is my expertise.

The way I would write this query is:

SELECT Quantity_Query.or_part_num, Quantity_Query.or_part_desc, 
       Sum(Nz(Per_Hours_Query.per_hours,0)) AS Hours, Sum(Nz(Quantity_Query.ol_quantity,0)) AS SumOfol_quantity
FROM   Quantity_Query 
LEFT  JOIN  Per_Hours_Query ON (Per_Hours_Query.part_desc = Quantity_Query.or_part_desc) 
                          AND (Per_Hours_Query.part_num = Quantity_Query.or_part_num)
WHERE  (((Quantity_Query.or_part_num) Like Forms!Part_Search!PartNo_Search & "*") 
   And ((Quantity_Query.or_part_desc) Like Forms!Part_Search!PartDesc_Search & "*"))
GROUP BY Quantity_Query.or_part_num, Quantity_Query.or_part_desc
ORDER BY Quantity_Query.or_part_num;

But this rewrite should not change the result. I change the order of the tables, so that the join becomes a LEFT JOIN instead. I get a headache when I see a RIGHT JOIN.

I moved the filter condition from HAVING to WHERE, since this is the more normal way to do it. Typically, you use HAVING to filter on aggregate values, for instance

   HAVING SUM(Per_Hours_Query.per_hours) > 0

You can't filter on SUM in WHERE; since logically it is evaluated before GROUP BY.

Now to your actual problem. It is quite obvious that the JOIN condition is not working as you intended. Why, I don't know, since I don't see your data. But maybe there is a difference in something invisible. Maybe it could be trailing spaces. In SQL Server trailing spaces does not matter in the = operator, but maybe it does in Access. It could be something silly like invisible control character. Had you been on SQL Server, I would have suggested that you convert the values to binary to compare, but I don't if Access even has a binary data type.

Answer 2

Hi @Baseel Atallah

Considering that you are using RIGHT JOIN in this query, which returns all records from the right table (Quantity_Query) and the matching records from the left table (Per_Hours_Query).

The result is 0 records from the left side, if there is no match. That is why the sum of ol_quantity seems normal while the sum of per_hours return 0.

It is suggested that you double check the result of join condition and move the having condition to where condition as Erland suggested.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".