Calculations using WHERE

[pickslides]

I am doing some counts and sums but when I use a WHERE for a criteria, it applies this to all the sums and counts.

How do I construct a query where the criteria only applies to a particular sum or count while keeping the others unaffected?

SELECT Students_Concern.UID, [Roll-Mark-Data].Surname, [Roll-Mark-Data].Preferred, [Roll-Mark-Data].[Year Level], Sum([Roll-Mark-Data].[Absence Minutes]) AS [SumOfAbsence Minutes], 44*Count([Attendance Status]) AS SAA, Count([Roll-Mark-Data].[Attendance Status]) AS [CountOfAttendance Status]
FROM Students_Concern LEFT JOIN [Roll-Mark-Data] ON Students_Concern.UID = [Roll-Mark-Data].[Student ID]
WHERE ((([Roll-Mark-Data].[Attendance Status])=[B]"School Acknowledged Absence"[/B]))
GROUP BY Students_Concern.UID, [Roll-Mark-Data].Surname, [Roll-Mark-Data].Preferred, [Roll-Mark-Data].[Year Level]

 

[June7]

WHERE is filtering records. Only records retrieved can be considered in calcs.

Alternative is IIf() expression to do conditional calc.

Count(IIf([Attendance Status]="School Acknowledged Absence",1,Null))

 

[arnelgp]

my suggestion is that you moved all other columns (ie, Surname, Preferred, Year Level) to a master table. putting it in a transaction file make your table (Roll-mark-data) not normalized.

 

[plog]

...FROM Students_Concern LEFT JOIN [Roll-Mark-Data] ON...
...WHERE ((([Roll-Mark-Data].[Attendance Status])=...

Also, when you apply criteria to a table in a LEFT JOIN (Roll-Mark-Data]) you no longer have a LEFT JOIN, you have an INNER JOIN. Your query no longer is showing all records from Students_Concern, but is showing only records that match between the 2 tables.

The way around this is to do a sub-query on Roll-Mark-Data, apply the criteria there, then LEFT JOIN that query and use it instead of Roll-Mark-Data.