I'm assuming that one of the fields will track the date and time the patient
arrived. Also this field will be a data/time datatype. In that case create a
query and have the following criteria in the date field.

Between [Report Start Date] and [Report End Date] +.99999

Base the report on this query. When run the query or report will ask you for
the dates that you want. One problem might be that the query might be
expecting the mm/dd/yy date format.

I'd strongly suggest having *two* tables - a table of Patients (which will be
relatively static) related one-to-many to a table of visits. You can then
easily use a Query to select only those visits within a certain range of
dates.

It's not at all clear from your post how your table is structured, or what is
being "refreshed" or updated.

            John W. Vinson [MVP]

Last week's appointments, but not those with diagnosis from a previous
week.
(not tested)

Assumes table Patients (patient_id, first_name, last_name)
and table PatientAppointments (patient_id, appt_date, diagnosis)

Query1:

SELECT patients.patient_id,
      patients.first_name,
      patients.last_name,
      patientappointments.appt_date,
      patientappointments.diagnosis
FROM   patients
      INNER JOIN patientappointments
        ON patients.patient_id = patientappointments.patient_id;

SELECT a.patient_id,
      a.first_name,
      a.last_name,
      a.diagnosis,
      a.appt_date
FROM   query1 AS a
WHERE  a.appt_date BETWEEN (DATE() - DATEPART("w",DATE()) + 1) - 7
                          AND (DATE() - DATEPART("w",DATE()) + 1) - 1
      AND a.patient_id NOT IN (SELECT b.patient_id
                               FROM   query1 AS b
                               WHERE  a.patient_id = b.patient_id
                                      AND a.diagnosis = b.diagnosis
                                      AND b.appt_date < (DATE() - DATEPART
("w",DATE()) + 1) - 7);