Process Access records and evaluate in groups for summarizing
Posted on 2004-11-14
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!!