Process Access records and evaluate in groups for summarizing

Hi everyone,

Sorry about the ambiguous title.  I had a hard time summarizing.

Anyway, I have two Access 2000 tables - Patient and ScheduledActivity.  They are related 1:M.  So, a Patient can have 1 - Many Sched Activities.

My goal is to have a report that shows the Sched Activity by Patient with some summarizing at the end given a date range.  I'm close, but there are a couple of fatal flaws.  The issue I'm trying to overcome is that a Patient may have repeating Sched Activities in the given date range, which skews the summary fields at the end of the report.  For instance, a Patient my have the following Sched Activities:
- Patient Called In, Review Info, Book Date Set, Patient Cancelled, Called Patient, Book Date Set

The primary concern is the repeating "Book Date" entries.  Basically, I only need to report on the last one.  Also, I don't want to report on the Book Date if a Patient Cancelled activity happened after the Book Date.  

Currently, I'm using this in my summary field to get the total number of Booked Patients: =Sum(IIf([SchedAction]="Book Date",1,0))
This is given me incorrect data, as I have explained above.

Basically, I feel like I need to process each record one a time and evaluate it, storing some of the previous records for that patient to make sure I haven't encountered a Patient Cancelled activity or a second (or more) Book Dates.  I do not know how to do this, or where to put the code to accomplish this in a report.


Thanks a ton!!
- Russell

Who is Participating?
Bob LambersonConnect With a Mentor Software EngineerCommented:
You will need to create a query like this to provide your record source for the report then your sum() will get the correct data.

SELECT Activity.patientID, Max( AS MaxOfdate
FROM Patient LEFT JOIN Activity ON Patient.patientId = Activity.patientID
WHERE (((Activity.cancelled)=0))
GROUP BY Activity.patientID;

All Courses

From novice to tech pro — start learning today.