By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,873 Members | 1,040 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,873 IT Pros & Developers. It's quick & easy.

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

P: 23
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,

Attached Images
File Type: gif sj1.gif (12.8 KB, 110 views)
File Type: gif sj2.gif (13.3 KB, 110 views)
File Type: gif sj3.gif (11.7 KB, 119 views)
Aug 23 '07 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 10K+
P: 12,374
Isn't this related to this?
Aug 24 '07 #2

P: 23
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?

Aug 24 '07 #3

Expert Mod 10K+
P: 12,374
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.
Aug 24 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.