Report using totals query not calculating on report

[juliesmomma]

I am creating a report to calculate total number of patients per month and it will look something like this...

April May June
1 1 9

I did a totals query where it counts where it is grouped by [Month] and counting Record# (CountOfRecord#]. The query calculates just fine. I get the correct totals.

On my report I am hiding the detail but creating these calculated fields.
=iif([Month]=4,[CountOfRecord#],0)
etc...for the rest of the months...

In the report footer I am referencing this field name.

I have created a report like this before but this time for some reason, I am only getting the total for the last month in the report footer, something like this....

April May June
0 0 9

I don't understand why it won't put the correct totals in the other 2 months. I looked at the detail section and it is putting the correct numbers there.

I know this probably sounds a bit confusing but could someone give advice on this?

Thanks in advance!

 

[dhookom]

Try:
April
=Abs( Sum([Month]=4 * [CountOfRecord#]))
May
=Abs( Sum([Month]=5 * [CountOfRecord#]))

Duane
MS Access MVP

 

[Clandon]

Have you thought of creating a crosstab query that calc's the month with a count of each record. You may need to create the month field based on another field? i.e MonthYear: Left ([Datefield],2)+"/"+Right([DateField],4) as the row and Records:Count([Records]) as the value?
Then create the report from the new query?
Hope this helps.

 

[dhookom]

If a Crosstab was used, it should be limited to one year of records and set the Column Headings to Format([DateField],"mmm&quot.

Duane
MS Access MVP

 

[juliesmomma]

Thanks for your replies. I used the crosstab query and that worked like a charm.

Thanks again!