Where do I Calculate Previous Record?

Hello,

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
appointments.

PAT ID 100
APPOINTMENT DAYS BETWEEN
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.

Thanks.


Tables
tblPatient
PATID, Autonumber, Key

tblAppointment
APPID, Autonumber, key
PATID, Number
APPDATE, Date

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] &
" AND
ApptDate #" & [ApptDate] & "#"),[ApptDate]),0)

"Peter Noneley" wrote in message
...
Hello,

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
appointments.

PAT ID 100
APPOINTMENT DAYS BETWEEN
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.

Thanks.


Tables
tblPatient
PATID, Autonumber, Key

tblAppointment
APPID, Autonumber, key
PATID, Number
APPDATE, Date

Thanks Roger and Bruce.

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

Your help is much appreciated.