Hi there,
I have this query, does not return what I am after, yet it does with the same logic in Excel with a Countifs
Just looking it count the number of times the number 49 appears in field Absence Minutes as my cross tab value
Code:
TRANSFORM Sum(IIf([Absence Minutes]=49,1,0)) AS CountOfMins
SELECT [Roll Mark Data].ID, [Roll Mark Data].Surname, [Roll Mark Data].Preferred, [Roll Mark Data].[Year Level]
FROM [Roll Mark Data]
GROUP BY [Roll Mark Data].ID, [Roll Mark Data].Surname, [Roll Mark Data].Preferred, [Roll Mark Data].[Year Level]
PIVOT [Roll Mark Data].[Roll Date];
Thanks, Q