Hi guys I have 3 separate queries which count the number of entries in a data set depending on set parameters. I have a summary query which simply collates the three queries counts and is the basis for a report.

Problem occurs when if one of the three individual queries returns no value the sum query returns no value also, even though the other two counting queries do have values.

I want the queries to return 0 if noting is there and show the correct info for the counts that are >0.. how can I do this?

Cheers
paul

Since you want to show all the values you are trying to count even if there are zero occurances, you'll need to build a table if you don't already have one. I can't tell from your post what your "real world" problem is so I'll make one up. You want to count all the students enrolled in each class.

Select ClassId, Count(*)
From tblEnrollment Inner Join tblClasses on tblEnrollment.ClassId = tblClasses.ClassId
Group by ClassId;

This will produce the following:

Class1 45
Class2 0
Class3 12
Class4 18

Without the inner join to tblClasses, the row showing the count for Class2 would not appear in the above resultset.

I'm trying to achieve similar results eg.

SELECT tbl_Assess_Level.Assess, Count(*)
FROM tbl_processed_data INNER JOIN tbl_Assess_Level ON tbl_processed_data.Assess = tbl_Assess_Level.Assess
GROUP BY tbl_Assess_Level.Assess;
where tbl_assess_level has values 1 - 4 and tbl_processed_data may or may not have values 1 - 4. I still get no records if count for a value is 0.

????????????????????????

You need to change the query to use a left join and you must select the assess from the tbl_assess_level table.

SELECT L.Assess, Count(*)
FROM tbl_Assess_Level as L LEFT JOIN tbl_processed_data as D ON L.Assess = D.Assess
GROUP BY L.Assess;