Countifs CrossTab not working

[pickslides]

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 to count the number of times the number 49 appears in field Absence Minutes as my cross tab value

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

 

[arnelgp]

I have this query, does not return what I am after

Then what are you after?
Is it equal or more than 49?

 

[pickslides]

Then what are you after?
Is it equal or more than 49?

It does count what I want but the row fields are repeated and the values are cascaded by date. Think the Group By has row headers is the problem

 

[arnelgp]

If you can setup an excel table for you table/query data on 1 sheet, and the expected result on another, it wiuld be easy to picture.

 

[Pat Hartman]

The crosstab is unnecessary.
Select Count(*) as CAM from [Roll Mark Data]
Where [Absence Minutes] = 49

PS, it is poor practice to use embedded spaces or special characters in object names.

 

[juke2huke]

i have a problem of schema on my website can anyone guide me?

 

[Pat Hartman]

Welcome aboard. Please don't hijack other threads. Start a new one with your question in the Tables section. You will also need to provide much more information if you want help from us. Start by posting your schema and telling us what problem you are trying to solve.