Hey everybody! This is my first post here, so hopefully I will be able to make it as clear as possible. I have a very large database (over 1,000,000 entries) and my current goal is to find out the number of 'sessions' that a person does during his or her lifetime in the database.
I have a table that has records of each person (each have their own unique ID), and the date of that person's visit. I have defined a session as being complete if the person does not come back for a visit in a 3 week period. After the 3 week period, if the person comes back for another visit, a new session is started.
There are thousands of unique persons in the database, and a person will often visit many many times.
My Goal: to determine how many 'sessions' each patient does.
Someone from another site has suggested using this code:
Code:
SELECT x.[Patient ID], Count(x.Date) AS CountOfSessions
FROM Query1 AS x
WHERE (((DateDiff("d",Nz((SELECT TOP 1 Date FROM Query1 WHERE [Patient ID] = x.[Patient ID] AND Date < x.Date ORDER BY Date DESC;),0),[Date]))>21))
GROUP BY x.[Patient ID];
but that Causes Access to crash (I enter it in the SQL code view of a query.
I am using Access 2003 on Windows XP if that helps.
Thank you so much for your time!
-Chris
Complex(?) Count Query Causing Access to Crash
