Calculations using WHERE (1 Viewer)

pickslides · Jul 3, 2019

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?

Code:

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 · Jul 3, 2019

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 · Jul 3, 2019

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 · Jul 3, 2019

Code:

...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.

Calculations using WHERE (1 Viewer)

pickslides

Red Sails In The Sunset

June7

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

plog

Banishment Pending

Similar threads

Users who are viewing this thread