problem with the query

[psandhya408]

I have two tables

one table has year,department code, slots available  for kindergarten,slots available for highschool

other table has disctrict code , grade level and funding code,student iD,year

I want the report output to be

year||district code|| slots available for kindergarten|| Actual no-of kindergarten students with(funding code=11 and grade level=454)|| slots available for highschool || Actual no-of high school students with(funding code=29 and grade level=300


I am trying to calculate the Actual no-of kindergarten students using count func

count( case when code=11 and grade level=454 then (1) else (0) end )  and similarly for other

But my numbers are not correct

Please help. Thanks.

[RKMI]

In your count, you need for [district code], [year].

i.e Count([Grade type] for [district code], [year])

Think the value you are getting is the lumped value of all district codes/ multi year.

Another thing I would do differently is first group the grade level then types of funnding codes. Prompt filter for Grade level/ Funding type used.

Thanks,
RK