Calculating a Total for each record source in a Report

  • Thread starter Thread starter mguffey5
  • Start date Start date
M

mguffey5

I have an attendance report that pulls data from a query that is linked to a
subform on a main forum. This query lists multiple individuals and if
they've attended the specific event as required. The query looks like the
below:

Cadet Name Flight PT 1 PT 2 PT 3 PT 4 PT 5 PT 6
Present Absent
Doe, John Alpha P P P P A
P 5 1
Doe, Jane Alpha A A A P P
A 2 4

I can get a sum total for all the "P" marks in in the Columns which gives me
an overall for all cadets. Again what I need is for each individual record
that shows on the report to have individual totals for each one.

As you can see above John Doe would have 5 Present and 1 Absent while Jane
Doe would have 2 present and 4 absent. What I need is to get a total of "P"
and "A" for each record source as shown above.

I'm having trouble figuring out what string of commands or expression to put
in the control source for the box to calculate this information.

Any help would be appreciated
 
mguffey5 said:
I have an attendance report that pulls data from a query that is linked to a
subform on a main forum. This query lists multiple individuals and if
they've attended the specific event as required. The query looks like the
below:

Cadet Name Flight PT 1 PT 2 PT 3 PT 4 PT 5 PT 6
Present Absent
Doe, John Alpha P P P P A
P 5 1
Doe, Jane Alpha A A A P P
A 2 4

I can get a sum total for all the "P" marks in in the Columns which gives me
an overall for all cadets. Again what I need is for each individual record
that shows on the report to have individual totals for each one.

As you can see above John Doe would have 5 Present and 1 Absent while Jane
Doe would have 2 present and 4 absent. What I need is to get a total of "P"
and "A" for each record source as shown above.

I'm having trouble figuring out what string of commands or expression to put
in the control source for the box to calculate this information.
Click to expand...


Although that looks to be denormalized data that should have
been calculated in the report's record source query, you can
do it using a text box epression like:

=-([PT 1]="P") - ([PT 2]="P") - ([PT 3]="P") - ([PT 4]="P")
- ([PT 5]="P") - ([PT 6]=P")
 
Back
Top