Calculation

[John]

I have a calculation to perform, and I'd like to avoid
writing a procedure to loop the database in order to
calculate it. I have a query that returns all
evaluations over a specified time period,

Evaluation Heart
Subject ID Date Rate
1 01/01/2001 90
1 02/01/2001
1 03/01/2001 77
2 01/08/2001
2 06/09/2001
2 07/11/2001
3 01/01/2001 80


I've put together a report that uses this query. I'd
like to put in the 'Control Source' of a control an
equation that will tell me what is the percentage of
individuals that had at least 1 heart rate taken during
the defined period. For the example above, the answer
would be,

2 (had at least one heart rate)
------------------------------- = 66%
3 (total individuals)


Any suggestions? Thank you, John

 

[Marshall Barton]

I have a calculation to perform, and I'd like to avoid
writing a procedure to loop the database in order to
calculate it. I have a query that returns all
evaluations over a specified time period,

Evaluation Heart
Subject ID Date Rate
1 01/01/2001 90
1 02/01/2001
1 03/01/2001 77
2 01/08/2001
2 06/09/2001
2 07/11/2001
3 01/01/2001 80


I've put together a report that uses this query. I'd
like to put in the 'Control Source' of a control an
equation that will tell me what is the percentage of
individuals that had at least 1 heart rate taken during
the defined period. For the example above, the answer
would be,

2 (had at least one heart rate)
------------------------------- = 66%
3 (total individuals)

Using Sorting and Grouping (View menu), create a group on
the SubjectID field and specify Yes for the Group Footer
property. You can make the group footer section invisible
if you don't want to see it.

To count the number of subjects, add a text box named
txtRunSubjects to the group footer section. Set its control
source expression to =1 and RunningSum property to Over All.

To count the number of subjects with at least one entry in
the Rate field, add another text box to the group footer.
Name this one txtRunRate and set its RunningSum to Over All
too. The control source expression would be:
=Abs(Count([Rate]) > 0))

Now you can use a text box in the Report Footer with the
expression:
=txtRunRate / txtRunSubjects
to display the percentage.