Where do I Calculate Previous Record?


Peter Noneley


I have a database with two tables, which keeps track of historical
patient appointment dates.

The tables both have AutoNumber key fields, PatID and AppID, and are
related on PatID.

I have a Form that shows an individual patient with their appointments
on a subform.

What I need now is to calculate how many days between each of their

PAT ID 100
01-JAN-09 0
08-JAN-09 7
20-JAN-09 11
23-JAN-09 2

I would like this calculation to show on a Form and Report.

What I have already tried.
- I looked on the web for info on calculating on previous records.
- It seems that Domain Aggregate functions are what I needed to use,
but I could not get them
to work as they looked at the key field AppID. The AppID is
incremented each time any appointment
is created, so although it is sequential, it is not sequential for
each specific patient.
- I looked at VBA code that tried to do the same thing, but it still
did not take into account
that I wanted to look at the previous record for a specific patient,
rather than the previous
record in the list of appointments.
- I then started looking at Running Sum in Queries, at this stage I
realised I was out of my
depth. (Which is not very deep at all!)

My questions:
(1) Do I do the calculation in a Query first, then base the Form /
Report on the Query?
(2) How do I do the calculation?

Any help will be appreciated.


PATID, Autonumber, Key

APPID, Autonumber, key
PATID, Number

Roger Carlson



Assuming PatID is the linking field between the main records and the
individual appointment records, make a query (I am assuming the table with
the appointment information is named tblAppt, but change it as needed)
including ApptDate (the appointment date field) and PatID. I would add
AppID also, as it is the key field, but it is not used in the expression.

At the top of a blank column in query design view (use a name of your choice
instead of SinceLast, if you like):

SinceLast: Nz(DateDiff("d",DMax("ApptDate","tblAppt","PatID = " & [PatID] &
ApptDate < #" & [ApptDate] & "#"),[ApptDate]),0)

Peter Noneley

Thanks Roger and Bruce.

I have used the info, and learnt a great deal !

Your help is much appreciated.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question