CODEWITHSUNDEEP
ms-accessms-access-reports
psycoperl
psycoperl

Reputation: 140

Microsoft Access - Reporting - In Group Header have multiple subtotals

I am working on a report in Microsoft Access (365). My query contains all the appointments that are issued. In the report I have grouped them by Cycles, TestDate/Time, and SubjectArea. While I am not breaking the list up by AdmitType (as I want to have an alpha sorted list for which students are testing a specific subject on the date/time) I would like to know how many are of each AdmitType for quick reference.

In the Group, Sort, Total panel I can see the option to do totals but am not sure how to write the expression to break it down. For example, I have 4 AdmitType codes in my system ("FR","TR","GR","UN"). So, I would ideally like to have my report in the Subject Header have

Subject Area: [Subject]

Admit Type FR: Count(*) Where Admit_Type="FR"

Admit Type TR: Count(*) Where Admit_Type="TR"

ScreenShot of Attempted Layout

Is something like this possible without having to do a sub report or add more columns to my existing query? If so, how.

If this were Excel, I would use a CountIFs function, but I am at a loss when it comes to MS Access.

Upvotes: 0

Views: 34

Answers (1)

psycoperl
psycoperl

Reputation: 140

Using the suggestion by @June7, I was able to get this to work using the following code

=Sum(IIF({MyConditions},1,0))

Where {MyConditions} represents the normal IIf statement conditional expression for what I am looking for.

Upvotes: 0

Related Questions