dates!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a patient database storing details of a patients consultations with
their doctor. Say I have a patient who has had 6 consultations...I need to
work out the time that elapsed between each consultation i.e. between
consultation one and consulation two, 7 weeks passed, between consultation
two and consulation three, 8 weeks passed...I then need to work out an
average of the time between appointments

How do I do this???

Thanks
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll need to create a "previous record" type query to get the ET
between consultations. To get the Average ET you'll need to run another
query on the first query.

Something like this:

Query1:
SELECT PatID, VisitDate,
DateDiff("ww", (SELECT MAX(VisitDate) FROM Visits WHERE PatID = V.PatID
AND VisitDate < V.VisitDate), VisitDate) AS WeeksBetweenVisits
FROM Visits As V

Query2:
SELECT PatID, Avg(WeeksBetweenVisits) As AvgWeeks
FROM Query1
GROUP BY PatID

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQVMgZ4echKqOuFEgEQLzrQCgkaADwMo2WJVeV0VexahTt87GVXUAoLx1
a6V9N5BdcvBvbaGts9v8pGcf
=15M4
-----END PGP SIGNATURE-----
 
Back
Top