count rows - don't show in report - show in group footer

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report based on a query.
The query extracts records for problems.
Problems may be unresolved or resolved.
I need to list on the report only the unresolved problems but I must also
print the count of resolved problems. How to do this in a query?
The problems are listed in order by Group. In the Group footer I must total
the unresolved problems listed and also the resolved problems for the group.
Lets say the Problem table columns are:
Group
ProblemID
ResolutionDate

Currently the report has sorting and grouping for the Problem Id and Group
with footer set to YES for the Group.
 
I need to list on the report only the unresolved problems but I must also
print the count of resolved problems. How to do this in a query?
The problems are listed in order by Group. In the Group footer I must total
the unresolved problems listed and also the resolved problems for the group.
Lets say the Problem table columns are:
Group
ProblemID
ResolutionDate

Currently the report has sorting and grouping for the Problem Id and Group
with footer set to YES for the Group.
Click to expand...

One handy way to do this is to put two calculated fields in the
Report's Recordsource query:

Resolved: IIF(IsNull([ResolutionDate]), 0, 1)
Unresolved: IIF(IsNull([ResolutionDate]), 1, 0)

Put a textbox on the section footer with a control source

=Sum([Resolved])

and the same for Unresolved.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top