Process Access records and evaluate in groups for summarizing

Posted on 2004-11-14
Last Modified: 2011-04-14
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

Question by:rilgenfr
    1 Comment
    LVL 12

    Accepted Solution

    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;


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now