P
Peter Noneley
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
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