Complex(?) Access Query/Excel Question

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.

I was thinking of exporting the Query that shows the patients and dates to Excel, and play with the data there, but as I said there are many many entries, and I am using Access/Excel 2003 so I am limited to about 65,000 rows in Excel.

I am a total novice in both Access and VBA, although quite experienced in Excel. Any help would be GREATLY appreciated!!

Thank you so much for your time, and I will try to make things more clear if there are any questions. Have a great day!

-Chris

Thanks very much for your help. I am still a bit confused though, where would I use that? In the Design portion of the Query? In VBA?

Thanks again!

In the design of the query itself. There is a tab that states "criteria."

This query doesn't do a count, it just leaves the first record that marks the beginning of a session.

Awesome, that's great!

Next question (sorry for so many!)

I am running the query as you said (hopefully, anyways).

How long would you expect it to take on about 1,00,000 entries, on a Core Duo machine at 2ghz? Only one core is being used, but it is being fully utilized. The query has been running for over 10 minutes now...is something going wrong?

For a count though, you could try:

Expand|Select|Wrap|Line Numbers

1. SELECT x.PatientID, Count(x.EventDate) AS CountOfSessions
2. FROM Query1 AS x
3. GROUP BY PatientID
4. HAVING (((DateDiff("d",Nz((SELECT TOP 1 EventDate FROM Query1 WHERE PatientID = x.PatientID AND EventDate < x.EventDate ORDER BY EventDate DESC;),0),[EventDate]))>21));
5.  

I would expect it to take a while. It's not just doing 1 million runs, it's doing multiple runs for each record so I expect it won't finish for a bit. I once did a non-cartesian join, joining 20,000 to 1.2 million records that that took 10 hours. I don't expect yours to take quite that long but it may be a while. If you want to see if it works, you could try grabbing a subset of the records in another table and then testing it on that.

It's going to take even longer because it has to sort the records and a million records is a lot to sort.

(Hopefully) last question in this thread:

Is there a way to safely cancel the query that is currently running, so that I can try it on a subset? Or alternatively, is the a way to monitor the progress of the running query and get an estimated completion time?

Scratch that, I just tested it. Although the previous may work, it's untested so to be safe it should be:

Expand|Select|Wrap|Line Numbers

1. SELECT x.PatientID, Count(x.EventDate) AS CountOfSessions
2. FROM Query1 AS x
3. WHERE (((DateDiff("d",Nz((SELECT TOP 1 EventDate FROM Query1 WHERE PatientID = x.PatientID AND EventDate < x.EventDate ORDER BY EventDate DESC;),0),[EventDate]))>21))
4. GROUP BY PatientID;
5.  

For the total noob:

Does x represent the name of the table? Or do I just leave x as 'x' ?

Control break stops it and is this somethign you are looking for???



declare @output varchar(100)
declare @i int
declare @st datetime

set @i = 0
set @st = getdate()
while @i < 2000
begin
select @output=OrderId from Northwind.dbo.Orders
set @i = @i + 1
end
print 'First batch completed in: ' +
rtrim(cast(datediff(ss,@st,getdate()) as char(10))) +
' seconds!'
set @i = 0
set @st = getdate()
while @i < 4000
begin
select @output=OrderId from Northwind.dbo.[Order Details]
set @i = @i + 1
end
print 'Second batch completed in: ' +
rtrim(cast(datediff(ss,@st,getdate()) as char(10))) +
' seconds!'

The only thing you need to change is PatientID or EventDate to match your field names and:
Query1 if you named the first query something else.
SomeTable to the original table.

This isn't Access is it? This is Oracle? or SQL Server?

Using this code:

Expand|Select|Wrap|Line Numbers

1. SELECT x.[Patient ID], Count(x.Date) AS CountOfSessions
2. FROM Query1 AS x
3. 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)) ;

Access give me this error:

"You tried to execute a query that does not include the specified expression 'Patient ID" as part of an aggregate function.



Notes: the original code had included the word Query1 in front of Patient ID and Date, as that is the name of the Query (SELECT Query1.[Patient ID], etc.)

And: CountOfSessions, is that an arbitrary name? The name of a table?

Thanks again for all your help

*Edited. Query name is indeed Query1

Additional Information: Modifying the above code to read:

Expand|Select|Wrap|Line Numbers

1. SELECT x.Query1.[Patient ID] AS Expr1, Count(x.Query1.Date) AS CountOfSessions
2. FROM Query1 AS x
3. WHERE (((DateDiff("d",Nz((SELECT TOP 1 Query1.Date FROM Query1 WHERE Query1.[Patient ID] = x.Query1.[Patient ID] AND Query1.Date < x.Query1.Date ORDER BY Query1.Date DESC;),0),[Query1].[Date]))>21));

gives me 3 windows when I try to run the query, asking me to enter a Parameter Value for x.Query1.PatientID and x.Query1.Date and Query1.Date.

Entering nothing for those 3 values (just clicking OK) gives me a CountOfSessions value of 0

Oh, and just to be clear on something.
1/1/07
1/14/07
1/31/07
Are considered the same session because there's less than 3 weeks in between visits even though the first visit is more than 3 weeks from the last visit. Is this what you want?

Use:

Expand|Select|Wrap|Line Numbers

1. SELECT x.[Patient ID], Count(x.Date) AS CountOfSessions
2. FROM Query1 AS x
3. 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))
4. GROUP BY x.[Patient ID];
5.  

CountOfSessions is another Alias as the name of a field can't be Count(x.Date)

Using that code causes Access to crash
"Microsoft office Acces has encountered a problem and needs to close. We are sorry for the inconvenience"

With the option to Repair my open database and restart Access.

Any ideas why?

No idea why, that's the same code I use except for different field names in my test.
Your database might be corrupted. Try a compact and repair, if that doesn't work, try importing the table into a new database and redo the query, and if that still doesn't work make a new table and new query in a new database.

I just upgraded to Office 2007, same problem with the crash.
Every time it crashes Access does compact and repair.

Does it matter that Query1 is based off of another Query?

Just x. not x.Query2, refer to post #27. And that very last VisitDate should be x. not Query2.

Alright, I think I am on the right track.

I am running this query right now:

Expand|Select|Wrap|Line Numbers

1. SELECT x.PatientID, Count(x.VisitDate) AS CountOfSessions
2. FROM Query3 AS x
3. WHERE (((DateDiff("d",Nz((SELECT TOP 1 VisitDate FROM Query2 WHERE PatientID = x.PatientID AND VisitDate < x.VisitDate ORDER BY VisitDate DESC;),0),[VisitDate]))>21))
4. GROUP BY x.PatientID;

Where Patientid and VisitDate are in located in Query 3.

The Query began to run with no errors, but I am worried that it may not be working as I began the query at about 9:00am EST and it is just about noon here, and the query is still chugging away.

I realize that there are a lot of entries and that it will take time (about 1.21 million entries), but how long is too long to wait?

Specs again: 2ghz Core Duo laptop with 1gig Ram. It's a shame that Access is only using one core, although it leaves me the ability to do other things on the computer.