calculate on find duplicates

[belsha]

I have a non-relational db with entries for patients each time they have a procedure. Did a find duplicates query to find multiple times the person had a procedure. Would like to calculate the times between the two procedures. How can I say person ID procedure date 1 - person ID (which should be the same as the first part of this) - procedure date 2 for EACH duplicate person? (Note: I did not design this DB!)

 

[raskew]

Understand that you didn't design the database. Does that necessarily
mean that you aren't allowed to normalize it and get it in proper running order?

From your description, it would be relatively simple to normalize it
using three primary tables plus a lookup table:


tblPatient with fields:
PatientID (autonumber)
PatientName
+ other non-changing data items (e.g., DOB, Sex, etc.)

tblVisits with fields:
VisitID (autonumber)
PatientID (long)
DteVisit(date)
ProcedureID(long)

TblPatientsAndVisits 'this serves as the joining table
'between tblPatient and tblVisits
PVID(autonumber)
PatientID(long)
VisitID(long)

Finally, a lookup table of procedures

tblProcedures with fields:
ProcedureID (autonumber)
ProcedureName

With this type of structure, there'd be little problem returning
the information you're after. This approach is similar to the
StudentsAndClasses database template offered by MS.