Number of Days between visits

[gstullo]

Good morning,



I have a table listing all patient visits to a clinic. The data contains variables such as PatientID, VisitNumber, and VisitDate...

I need a query which returns the number of days between each subsequent visit per patient (Number of days between Visit 1 and Visit 2, Visit 2 and Visit 3, etc.).

I am sure that there is a simple solution but I and not thinking of it at the moment.

Any ideas?

Thanks,

GST

[jzwp11]

You will need a query that contains either a DLookup() function or a nested query to pull the date of the previous visit. Then once you have the date, you can use the datediff() function to get the number of days difference. Below is what the query with nest query would look like. The nested query is shown in red:

SELECT tblPatientVisits.PatientID, tblPatientVisits.VisitDate, (Select top 1 Q1.VisitDate from tblPatientVisits as Q1 WHERE Q1.patientID= tblPatientVisits.PatientID AND tblPatientVisits.VisitDate>Q1.VisitDate ORDER BY Q1.PatientID, Q1.VisitDate Desc) as PreviousVisit, dateDiff("d",tblPatientVisits.VisitDate, PreviousVisit) as DaySinceLastVisit
FROM tblPatientVisits;

[gstullo]

Thank you for the feedback. Nested queries... This is new to me and is like a light-blub going off over my head. I have had many situations where I have had to create, in the QBE grid, queries which are imported into queries. The result has been many, many queries to display a large summary in one plate. For example, a unique query for each year which sums the number of visits for that year and a large query which pulls all those small queries together to display it in one place.

It seems that the Nested would circumvent this...

I assume that is must be written in SQL and cannot be represented in the QBE grid?

-GST

[jzwp11]

I assume that is must be written in SQL and cannot be represented in the QBE grid?

Yes, the nested query will have to be written via the SQL view.
You can build the basic parts of the query in the QBE grid and then switch over to SQL view.

[gstullo]

Love it!

Thank you!

[jzwp11]

You're welcome