Complex Access Query: Indicate Start and End of new 'Session'

[Chris Gilpin]

Hey everyone, this one is a doozie (for me at least)

I have a large table (>1M entries) that contains Client IDs and Visit Dates

I am trying to create a query that will indicate when a client starts a new session and the date of, and when the client ends a session and the date of termination.

A session is ended if the client is inactive for X (say 21) days. I have done this by exporting the table to Excel and writing code in Excel, but I would like a solution to be done in Access if possible (much cleaner) so I don't have to keep jumping between applications.

Attached is the basics of what I have done in Excel. I then created a 'condensed' worksheet with just the 'Start' and 'End' dates for each patient.

Cell M1 contains the number of days that I want to classify as the cut-off for a new session (X, or right now 21)

I would be surprised if I asked my question in a clear manner, so please ask more questions so I can help people understand my problem.

This site is great, and I love the help I have gotten before. Thank you so much for your time,

-Chris

[Rabbit]

Isn't this related to this?

[Chris Gilpin]

Isn't this related to this?

Yup, it is for the same project, but the needs of the query are a bit different, so I put it in a new thread. Should I delete this one and repost the question in the old thread?

-Chris

[Rabbit]

No, it's fine if it's a different question. But if it's related to an older post it would help the people reading this to be able to see it. Did the query ever work? I don't think the count will work but the query before the count should at least give you the first record of each session.

If you then take that query, you should be able to use it as a subquery to get your start and end periods. I know the query before I implemented the count works, I tested it with some sample data. But the rest is off the top of my head but it should work.