Solved

All hours represented within a time range.

Posted on 2006-07-17
79
653 Views
Last Modified: 2011-09-20
I'm looking to determine hourly patient volume for our hopsital Emergency Department. I have each patient's arrival and departure times. For each hour within the period defined by these two markers, I would like to count the number of patients present.

I can easily determine the # of patients arriving and the # of patients leaving within a given hour. I need help counting the hours represented for those patients neither arriving nor leaving during a given hour.

Any help is appreciated.
0
Comment
Question by:tscott_72
  • 28
  • 25
  • 23
  • +2
79 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 17123858
h---------h---------h---------h--------h
     ^-arr                              ^-dep
            this   ^   and   ^  ??

If so
Select DateDiff("h",Arr, Dep)-1 as PatientWholeHours from myTable;
0
 

Author Comment

by:tscott_72
ID: 17123968
I appreciate the help. However, datediff will only tell me the # of elapsed hours between Arrival and Departure. I would like a way to count the presense of a patient for EACH hour within the defined time range. I was thinking, for example, of a way to create a field within each record with a comma-separated value string that indicates the hours (e.g. 01:00-1:59=1, 23:00-23:59=23, etc). I don't know how to do that... I'm open to any other soultions.

Thanks.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17124121
You could try a count with a group by using the DateDiff that GRayL has suggested
0
 

Author Comment

by:tscott_72
ID: 17124209
That will give me a grouping of patient "durations" or Lenghts of Stay in the ED. I need the patient volume for each hour and not just their length of stay. Thanks.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17124274
OK, but is the answer to my example 2 or four?  

 
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 17124441
GRayL>>from the questioner's discussion, your example would be a count of 1 for the FOUR hour-long intervals.  He/she wants the COUNT by hour, for each hour of the stated time period.

That is, in each hour interval, how many patients were present - which is quite different from how long did any one patient stay.

This is a non-trivial request.

It can be handled with some VBA coding, but not in a direct SQL Query.

AW
0
 

Author Comment

by:tscott_72
ID: 17124511
YES, Mr. Wood. YOU understand. I try not to ask trivial questions. I am open to a VBA solution!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17124514
AW:  I'm aware of that, but the asker's last sentence is why I posted my example.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17124856
tscott_72:  And the answer is?  I am still waiting to hear from you how many hours are in my example.
0
 

Author Comment

by:tscott_72
ID: 17124910
?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17124967
You wrote:  "I need help counting the hours represented for those patients neither arriving nor leaving during a given hour."  From that I assume my example would be 2 hours, or four - because the patient had been in for part of the first hour, two full hours, plus part of the fourth.  
0
 

Author Comment

by:tscott_72
ID: 17125165
ah, sorry I misunderstood. The answer would be four hous. I would like to count the part hours at the beginning and end of the patient's stay, as well as the full hours in-between.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17126243
I think the answer lies in this crosstab query.  

TRANSFORM Count([b.PatID])
SELECT a.hr
FROM hrs AS a INNER JOIN Admissions AS b ON (a.hr<=Hour(b.DepDT)) AND (a.hr>=Hour(b.ArrDT))
WHERE b.ArrDT Between Date()-14 and Date()
GROUP BY a.hr
PIVOT Format([b.ArrDT],"yyyy-mm-dd");

This requires that you have a separate table 'Hrs' with a single field 'hr' - type integer.  Assuming your main table is named Admissions with two datetime fields named ArrDT and DepDT on my testdata (very limited) I got excellent results:

TRANSFORM Count([b.PatID])
SELECT a.hr AS Hour
FROM hrs AS a INNER JOIN Admissions AS b ON (a.hr<=Hour(b.DepDT)) AND (a.hr>=Hour(b.ArrDT))
WHERE b.ArrDT BETWEEN Date() - 14 AND Date()
GROUP BY a.hr
PIVOT Format([b.ArrDT],"yyyy-mm-dd");

This will give you a recordset with 24 rows for each hour and 15 columns - Hour plus 14 day sums.

Because of the cut-off dates, the data 'grows' for the first few records in the first day and 'dies' in the the last few hours of the last day.

0
 
LVL 44

Expert Comment

by:GRayL
ID: 17126249
I should have added that the Hrs table requires the values 0-23.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 17127520
GRayL, that will only get the beginning of the period and the end of the period, but will not count the hours between the beginning and the end, for which a record does not exist in the Admissions table.

AW
0
 

Author Comment

by:tscott_72
ID: 17129870
GrayL: I haven't tried your solution as yet. Is AW correct? Will it count the hours in between or just the beginning and end of a patient stay?
0
 

Author Comment

by:tscott_72
ID: 17130354
I tried it. It returns an empty query when run from the SQL view. When I try switching to the design view it reports that Access can't represent the join expression.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17130942
With data like this that goes on for 200 records covering the whole day of 17 Jul

PatID      ArrDT      DepDT
1000      2006-07-17 00:14      2006-07-17 12:10
1001      2006-07-17 00:24      2006-07-17 20:47
1002      2006-07-17 00:26      2006-07-18 08:20
1003      2006-07-17 00:32      2006-07-17 21:03
1004      2006-07-17 00:32      2006-07-17 14:08
1005      2006-07-17 00:40      2006-07-18 19:49
1006      2006-07-17 00:41      2006-07-18 18:18
1007      2006-07-17 00:51      2006-07-17 19:55
1008      2006-07-17 00:53      2006-07-18 15:05
1009      2006-07-17 00:59      2006-07-18 11:24
1010      2006-07-17 01:01      2006-07-17 15:10
1011      2006-07-17 01:07      2006-07-18 00:38
1012      2006-07-17 01:11      2006-07-17 23:10
1013      2006-07-17 01:16      2006-07-17 15:10
1014      2006-07-17 01:23      2006-07-18 06:49

Running this query:

TRANSFORM Count([b.PatID])
SELECT a.hr
FROM hrs AS a INNER JOIN Admissions AS b ON (a.hr<=Hour(b.DepDT)) AND (a.hr>=Hour(b.ArrDT))
WHERE b.ArrDT>=#2006-07-17#
GROUP BY a.hr
PIVOT Format([b.ArrDT],"yyyy-mm-dd");

It returns this result:

hr      2006-07-17
0      10
1      18
2      25
3      30
4      42
5      44
6      52
7      53
8      60
9      60
10      62
11      63
12      58
13      64
14      61
15      62
16      55
17      49
18      43
19      40
20      31
21      26
22      22
23      12

AW:  Why do you think the query is only picking up the first and last hours?  That is the purpose of my Hrs table - to provide a record for every hour including the end points.

tscott_72:  Can you provide the structure and a few records from your table.  I am assuming you created Hrs with a single ingeger field Hr - 24 records containing the values 0-23?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17130970
I'm not saying this is the complete answer.  However, it is a starting point from which I think we can hone in on a solution that resolves the end point problem.
0
 

Author Comment

by:tscott_72
ID: 17131661
yeah, looks like my tables and queries are right. I will gladly send them to you, but how would I do so?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17132072
you can upload your mdb at:

http://www.ee-stuff.com/accessLogin.php?returnURL=%2FExpert%2FUpload%2Fupload.php

The max size allowed is 4 mbytes so upload the zipped file.  That always saves a lot of room unless the mdb is small.
0
 

Author Comment

by:tscott_72
ID: 17132230
well, I uploaded the dbase...though not sure if I did it correctly. Let me know.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17132547
Its says the file is empty.  Make sure you copy the URL of your question located in the Address window of your browser into the Question box of ee-stuff before browsing to find your zipped file.

If the mdb is less than one meg, try uploading without zipping.

0
 

Author Comment

by:tscott_72
ID: 17132653
done.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17132770
OK your data started back 31 May 2006. Change the query I gave you to the following:

TRANSFORM Count([b.PT#])
SELECT a.hr AS Hour
FROM hrs AS a INNER JOIN Admissions AS b ON (a.hr<=Hour(b.DepDT)) AND (a.hr>=Hour(b.ArrDT))
WHERE b.ArrDT >=#2006-05-31#
GROUP BY a.hr
PIVOT Format([b.ArrDT],"yyyy-mm-dd");


Tell me if you think it looks right.
0
 

Author Comment

by:tscott_72
ID: 17132804
This is fantastic! You solved my problem and once I understand how it works...you will have taught me something new and valuable. Thanks.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17132997
Why do I see AutoMod reposting pointers to this question?  You weren't giving up on me were you? ;-)  A small lesson for us both.  Make sure each other knows the appropriate date range - wot??  Here I 'invented' yesterday's data and you are working with June's data.  No wonder you drew a blank.

The a and b are just aliases saving me typing out the longer table names.  By introducing the Hrs table it 'creates' a new record of b for every a.hr that is between Hour(b.arrDT) and Hour(b.depDT.  The TRANSFORM simply counts the instances of [b.PT#] that fit into every hour of every day.

Now in reading this over I think I see a flaw - perhaps what AW was referring to.  To do when a patient is in over midnight.  I'll get back to you.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17133031
Here is another approach to your question.  I will only post it because I marked the question to come back to because it was kind of interesting to find a solution for.  I'm sure GrayL solution will work and this is just another approach.

I used a temp table that will basically hold a detail record for each hour within the selected arrival and departure date/times.  This assumes that the arrival and departure times are stored as 07/18/06 8:00 AM.  The code will loop through each hour and count the number of patients that were present during that time slot.

My test tables look like ...

PatientTbl
     Patient_ID
     Arrival_Time
     Departure_Time

PatientTbl_Temp
     Hour_Detail
     Num_of_Patients

To test simply place this code behind command button on your form.  Also, you can eventually get the start and end dates from text boxes on your form.

Private Sub Command62_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From PatientTbl_Temp;"

Dim rst As Recordset
Dim rst2 As Recordset
Dim dteArrival As Date
Dim dteDepart As Date
Dim dteStart As Date
Dim dteEnd As Date
Dim dteCounter As Date

dteArrival = #7/18/2006 7:00:00 AM#
dteDepart = #7/18/2006 5:00:00 PM#

Set rst = CurrentDb.OpenRecordset("Select * From PatientTbl Where Arrival_Time >= #" & dteArrival & "# AND Departure_Time <= #" & dteDepart & "#;", dbOpenDynaset)
Set rst2 = CurrentDb.OpenRecordset("PatientTbl_Temp", dbOpenDynaset)

'Round arrival date down to the previous whole hour
dteStart = DateAdd("n", -(DatePart("n", dteArrival)), dteArrival)
dteStart = DateAdd("s", -(DatePart("s", dteStart)), dteStart)

'Round departure date up to the next whole hour
dteEnd = DateAdd("s", 60 - DatePart("s", dteDepart), dteDepart)
dteEnd = DateAdd("n", 60 - DatePart("n", dteEnd), dteEnd)


If rst.RecordCount > 0 Then

 'Number the temp table with 1 hour increments starting with the arrival time selected.
 Do Until dteStart > dteEnd
    rst2.AddNew
    rst2!HOUR_DETAIL = dteStart
    rst2.Update
    dteStart = DateAdd("h", 1, dteStart)
 Loop
 
 'Count the number of times each patient falls within the hour time slots.
 rst2.MoveFirst
    Do Until rst2.EOF
        rst2.Edit
        rst2!NUM_OF_PATIENTS = DCount("[PATIENT_ID]", "PatientTbl", "[ARRIVAL_TIME]<=#" & rst2!HOUR_DETAIL & "# AND [DEPARTURE_TIME]>=#" & rst2!HOUR_DETAIL & "#")
        rst2.Update
       
        rst2.MoveNext
    Loop
 
 
End If

rst.Close
rst2.Close
   
   
End Sub


ET

0
 

Author Comment

by:tscott_72
ID: 17133039
I wasn't quitting..I just don't know my way around EE yet. So, are you saying that if a patient is in our ED longer than 24 hours, the second instance of the same hour is not counted? For example, pt enters 23:00 hrs on day one and leaves 00:00, 25 hrs later. 23:00 and 00:00 can hopefully be counted twice. Not a frequent event, but from time to time a patient does stay more than 24 hours in the ED.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17133079
You would then base your report on PatientTbl_Temp and add the following to the OnClose event of your report to clear out the temp table.

Private Sub Report_Close()
     DoCmd.SetWarnings False
     DoCmd.RunSQL "Delete * From PatientTbl_Temp;"
End Sub

ET
0
 

Author Comment

by:tscott_72
ID: 17133081
Etsherman:

Will your VBA solution count repeat hours during a patient's stay, as when they stay more than 24 hrs?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17133099
No, I say the between function is not true when crossing midnight.  Between 23 and 1 should produce 3 instances but it produces 23 - I think.  Still working on it.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17133165
tscott 72 ...

That's whay I used a temp table to bascially build a timeline incremented by 1 hour that covers the arrival and departure range you would enter.  Example .... If you enter a range of  ...

dteArrival = #7/18/2006 7:15:00 AM#
dteDepart = #7/18/2006 4:30:00 PM#

My solution will first build in the temp table the field named Hour_Detail as shown below.

HOUR_DETAIL                           NUM_OF_PATIENTS
7/18/2006 7:00:00 AM
7/18/2006 8:00:00 AM
7/18/2006 9:00:00 AM
7/18/2006 10:00:00 AM
7/18/2006 11:00:00 AM
7/18/2006 12:00:00 PM
.....
.....
7/18/2006 5:00:00 PM


Then the code will loop through each Hour_Detail record and count the number of patients from your PatientTbl where the Arrival Date is less than or equal to the Hour_Detail and the Departure Date is greather than or equal to the Hour_Detail.  

Once you create the hour detail between the arrival and departure date/time then you just simply count the patients whose dates fit the timeslots.

ET




0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17133921
tscott 72 , you asked ...

>>>>>>Etsherman:

Will your VBA solution count repeat hours during a patient's stay, as when they stay more than 24 hrs?<<<<<<


Yes, because when the code builds the field "Hour_Detail" in the temp table it will continue pass 24 hrs.

Example ...

7/18/2006 11:00:00 PM
7/19/2006 12:00:00 AM
7/19/2006 1:00:00 AM


ET

0
 
LVL 44

Expert Comment

by:GRayL
ID: 17134018
So does mine.  I have one small snag.  But I have to go sail boat racing now.  Be back tomorrow.
0
 

Author Comment

by:tscott_72
ID: 17134152
ET:

A few observations: the temp table is populated with dates without an associated time (one such record per date)... perhaps times that were not counted. In looking over the underlying dataset, I see cases where an hour should be counted but isn't. Consider this:

the temp table says that there is 1 6/12/2006 patient for 05:00 hrs.

the underlying recordset shows two that meet the intended criteria.

Patient_id      arrival_time                      departure_time
30916233      6/12/2006 2:35:00 AM      6/12/2006 5:00:00 AM
30916258      6/12/2006 5:25:00 AM      6/12/2006 9:20:00 AM

1 of these records isn't being counted. Is the record that is not being counted being pickled up by the record for 6/12/2006 that has no time (as mentioned above)?
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17134167
tscott 72 ...

1.) You can replace the section of my code (2 lines) where I am "Rounding departure date up to the next whole hour with the following.  This will keep the code from rounding up if the minutes and seconds = 0.

'Round departure date up to the next whole hour
If DatePart("s", dteArrival) = 0 Then
    dteEnd = dteDepart
Else
    dteEnd = DateAdd("s", 60 - DatePart("s", dteDepart), dteDepart)
End If

If DatePart("n", dteEnd) = 0 Then
    'Do Nothing
Else
    dteEnd = DateAdd("n", 60 - DatePart("n", dteEnd), dteEnd)
End If


2.) Also, if you are going to prompt the user for the Arrival and Departure range from a form then you may want to setup a query based on your PatientTbl using this range so that the DCount function can use this query as opposed to the PatientTbl. In other words, filter the PatientTbl down to the range you are working with for the DCount function.  This will maintain performance of the DCount function down the road as your PatientTbl grows.

ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17134289
tscott ...

All records in the Field "Hour_Detail" in the Temp Table should include the full date and time like 6/12/2006 2:35:00 AM.  

Can you confirm that the field Type for Hour_Detail in the Temp table is Date/Time.

Also, where are you getting the value for these parameters that's included in the code above??

dteArrival = #7/18/2006 7:00:00 AM#
dteDepart = #7/18/2006 5:00:00 PM#
 

Table properties should be set as follows ...

PatientTbl_Temp
     Hour_Detail      Type = Date/Time
     Num_of_Patients   Type = Long

ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17134490
tscott 72

Using your last sample shown below, if I plug the patient id's and times into my test tables then run the code using a range of dteArrival = #6/12/2006 2:00:00 AM#  dteDepart = #6/12/2006 10:00:00 AM#  you should get the results that I got.

Patient_id     arrival_time                     departure_time
30916233     6/12/2006 2:35:00 AM     6/12/2006 5:00:00 AM
30916258     6/12/2006 5:25:00 AM     6/12/2006 9:20:00 AM


MY Results: PatientTbl_Temp

Hour_Detail                          Num_Of_Patients
6/12/2006 2:00:00 AM                    0
6/12/2006 3:00:00 AM                    1
6/12/2006 4:00:00 AM                    1
6/12/2006 5:00:00 AM                    1
6/12/2006 6:00:00 AM                    1
6/12/2006 7:00:00 AM                    1
6/12/2006 8:00:00 AM                    1
6/12/2006 9:00:00 AM                    1
6/12/2006 10:00:00 AM                  0

Keep in mind the Hour_Detail field in every hour on the exact hour.  Therefore, when 2:00 AM came around on 6/12/06 neither of the two patients were logged into the ER.  Patient 30916233 will be counted first at the 3:00 AM hour.  Same with patient 30916258.  He/she will be counted first at 6:00 AM on 6/12/06.

If that's not the way you want to count them then let me know.

ET


0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:tscott_72
ID: 17135116
I don't mean to be a stickler... this solution is wonderful. If possible, I'd like to include in the counts all arrivals that occur within a 59 min hourly range, and not just every hour on the hour. Although the patient may not be present for the full hour, I need to draw the line someplace and arrival time is a reasonable way to go.

Also, did you see any records in the temp file where there was no hour, just a date  and a count? For example:

hour_detail      num_of_patients
6/12/2006                                      9
6/12/2006 1:00:00 AM      8
6/12/2006 1:00:00 PM      7

Is this caused  by the arrivals that aren't counted until the next hour? what hour(s) do those 9 patients belong to?
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17135631
tscott 72 ....

Not a problem.  To answer your question regarding the records in the temp table with no time that's not a problem.  Actually that is Midnight or 12:00:00 AM.  When the table is filled from the code and the range crosses over into another day you should see ....

Hour_Detail
.....
6/12/2006 10:00:00 PM
6/12/2006 11:00:00 PM
                   6/13/2006   ' This is midnight or 12:00:00 AM, Access stores 00:00:00 for the time. See note below.
6/13/2006 1:00:00 AM
6/13/2006 2:00:00 AM
.......  

In table design mode, enter  m/d/yyyy hh:nn:ss AM/PM  for the Format property of the Hour_Detail Field and you will see the 6/13/2006 12:00:00 AM displayed.

Since this is a Date/Time field if you sort Hour_Detail Ascending you will get the correct sort order.

I will address your other question in another post so this one does not get to lengthy.

ET


0
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 200 total points
ID: 17135933
tscott 72 ....

Your said ...

>>>>I don't mean to be a stickler... this solution is wonderful. If possible, I'd like to include in the counts all arrivals that occur within a 59 min hourly range, and not just every hour on the hour. Although the patient may not be present for the full hour, I need to draw the line someplace and arrival time is a reasonable way to go.<<<<<

Being an Accountant, that's exactly where I was headed and was going to suggest to you.  That why if you notice in the temp table I coded the Hour_Detail field to round down to the whole hour for the start or arrival date then increment by 1 hour from there.  To accomplish your above request simply follow the steps below.

1.) In Table Design mode for the PatientTbl_Temp insert a row after the current Hour_Detail field, create the exact same field and name it Hour_Detail2.  Include this for the format of both fields    m/d/yyyy hh:nn:ss AM/PM.  

The new code will basically create a time slot range (2:00:00  2:59:00, etc., etc.)  based on the Arrival and Departure range entered (see sample below).  Note: Say if a patient departs at "exactly" 5:00:00 AM they will not be counted in the time slot 5:00:00 AM  5:59:00 simply because they are gone/not there.  If a patient arrives at "exactly" 5:59:00 then they will be counted in the time slot 5:00:00 AM  5:59:00 because they are there.  

HOUR_DETAIL                      HOUR_DETAIL2            NUM_OF_PATIENTS
6/12/2006 2:00:00 AM      6/12/2006 2:59:00 AM      1
6/12/2006 3:00:00 AM      6/12/2006 3:59:00 AM      1
6/12/2006 4:00:00 AM      6/12/2006 4:59:00 AM      1
6/12/2006 5:00:00 AM      6/12/2006 5:59:00 AM      1


2) Here is the modified function.  Just replace ALL previous code with this code.

Function CountPatients()

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From PatientTbl_Temp;"

Dim rst As Recordset
Dim rst2 As Recordset
Dim dteArrival As Date
Dim dteDepart As Date
Dim dteStart As Date
Dim dteEnd As Date


dteArrival = #6/12/2006 2:00:00 AM#
dteDepart = #6/12/2006 5:00:00 AM#

Set rst = CurrentDb.OpenRecordset("Select * From PatientTbl Where Arrival_Time >= #" & dteArrival & "# AND Departure_Time <= #" & dteDepart & "#;", dbOpenDynaset)
Set rst2 = CurrentDb.OpenRecordset("PatientTbl_Temp", dbOpenDynaset)

'Round arrival time to the whole hour
dteStart = DateAdd("n", -(DatePart("n", dteArrival)), dteArrival)
dteStart = DateAdd("s", -(DatePart("s", dteStart)), dteStart)

'Round departure time to the whole hour
 dteEnd = DateAdd("n", -(DatePart("n", dteDepart)), dteDepart)
 dteEnd = DateAdd("s", -(DatePart("s", dteEnd)), dteEnd)

If rst.RecordCount > 0 Then

 'Number the temp table with 1 hour increments starting with the arrival time selected.
 Do Until dteStart > dteEnd
    rst2.AddNew
    rst2!HOUR_DETAIL = dteStart
    rst2!HOUR_DETAIL2 = DateAdd("n", 59, dteStart)
    rst2.Update
    dteStart = DateAdd("h", 1, dteStart)
 Loop
 
 'Count the number of times each patient falls within the hour time slots.
 rst2.MoveFirst
    Do Until rst2.EOF
        rst2.Edit
        rst2!NUM_OF_PATIENTS = DCount("[PATIENT_ID]", "PatientTbl", "[ARRIVAL_TIME] <= #" & rst2!HOUR_DETAIL2 & "# AND [DEPARTURE_TIME]> #" & rst2!HOUR_DETAIL & "#")
        rst2.Update
       
        rst2.MoveNext
    Loop
 
 
End If

rst.Close
rst2.Close

End Function


ET

0
 

Author Comment

by:tscott_72
ID: 17138103
I get a run-time error...Item not found in this collection on this part of the code:

rst2!HOUR_DETAIL2 = DateAdd("n", 59, dteStart)
0
 
LVL 44

Accepted Solution

by:
GRayL earned 300 total points
ID: 17139040
I'm satisfied with this.  It does not count an instance where the DepDT is null because the patient is bedded elsewhere than Emergency:

TRANSFORM Count([PT#])
SELECT TimeSerial([Hrs].[hr],0,0) AS TimeOfDay
FROM admissions, Hrs
WHERE (DateValue(ArrDT)+TimeSerial([Hrs].[hr],0,0)) Between dateadd("n",-minute(ArrDT),ArrDT) and dateadd("n",-minute(DepDT),DepDT)
AND DepDT Is Not Null
GROUP BY TimeSerial([Hrs].[hr],0,0)
PIVOT Format(ArrDT,"yy-mm-dd");
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17139117
Albeit, at a somewhat later time.
0
 

Author Comment

by:tscott_72
ID: 17139238
The data will be such that the departure time is populated with either the ED discharge time or, in the cases of admissions to the hospital through the ED, the time the patient is transferred from the ED to a patient unit. So, nulls won't be an issue unless the data is missing.

It looks as though each hourly increment includes the counts within a 59 minute range, rather than just every hour on the hour? This is the desired treatment... please confirm.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17139480
It should be every hour on the hour.  Why do you say a 59 minute range?
0
 

Author Comment

by:tscott_72
ID: 17139790
for example: if a patient arrives in the ED at 05:25 hrs. I'd like to count the patient towards the hourly (5-5:59) volume stats. By every hour on the hour, such a patient would not be counted until 06:00 hrs since they were not present at 05:00 hrs. 59 minute range, in this case, means any time between 05:00-05:59 hrs. It looks as though your query would count such a patient for 05:00, as intended? please confirm.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17139831
tscott 72 .... You said ...

>>>>>I get a run-time error...Item not found in this collection on this part of the code:

rst2!HOUR_DETAIL2 = DateAdd("n", 59, dteStart)<<<<<<<

Seems like you did not perform step 1 from my last comments, see below.



1.) In Table Design mode for the PatientTbl_Temp insert a row after the current Hour_Detail field, create the exact same field and name it Hour_Detail2.  Include this for the format of both fields    m/d/yyyy hh:nn:ss AM/PM.


ET
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17139953
I had to modify the between clause to cater to the event of a patient arriving and leaving within the same hour.  Check your first patient on 2006-05-31. Prior to my fix, 2006-05-31 00:00:00 was not between 2006-05-31 01:02:00 and 2006-05-31 01:30:00 so that hour was blank.  Now it correctly shows one was in the ED during that hour.  

We are mixing up our terms.  I most certainly include the 05:25 ED arrival in the 05:00 to 05:59 period.  
0
 

Author Comment

by:tscott_72
ID: 17140161
Your solution is nice: SQL-based and effective. Thank you. I will have to study it so that I can understand completely how it works. Thank you.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17140179
tscott 72 ...

Using your sample database that was uploaded to ee-stuff.com I ran my function for the following range ...

Arrival Time = 05/31/2006 12:00:00 AM
Departure Time = 06/01/2006 12:00:00 PM

Here are the results ...

HOUR_DETAIL                      HOUR_DETAIL2             NUM_OF_PATIENTS
5/31/2006 12:00:00 AM      5/31/2006 12:59:00 AM      0
5/31/2006 1:00:00 AM      5/31/2006 1:59:00 AM      1
5/31/2006 2:00:00 AM      5/31/2006 2:59:00 AM      0
5/31/2006 3:00:00 AM      5/31/2006 3:59:00 AM      1
5/31/2006 4:00:00 AM      5/31/2006 4:59:00 AM      0
5/31/2006 5:00:00 AM      5/31/2006 5:59:00 AM      0
5/31/2006 6:00:00 AM      5/31/2006 6:59:00 AM      4
5/31/2006 7:00:00 AM      5/31/2006 7:59:00 AM      6
5/31/2006 8:00:00 AM      5/31/2006 8:59:00 AM      6
5/31/2006 9:00:00 AM      5/31/2006 9:59:00 AM      9
5/31/2006 10:00:00 AM      5/31/2006 10:59:00 AM      13
5/31/2006 11:00:00 AM      5/31/2006 11:59:00 AM      14
5/31/2006 12:00:00 PM      5/31/2006 12:59:00 PM      12
5/31/2006 1:00:00 PM      5/31/2006 1:59:00 PM      11
5/31/2006 2:00:00 PM      5/31/2006 2:59:00 PM      12
5/31/2006 3:00:00 PM      5/31/2006 3:59:00 PM      15
5/31/2006 4:00:00 PM      5/31/2006 4:59:00 PM      14
5/31/2006 5:00:00 PM      5/31/2006 5:59:00 PM      14
5/31/2006 6:00:00 PM      5/31/2006 6:59:00 PM      14
5/31/2006 7:00:00 PM      5/31/2006 7:59:00 PM      6
5/31/2006 8:00:00 PM      5/31/2006 8:59:00 PM      7
5/31/2006 9:00:00 PM      5/31/2006 9:59:00 PM      6
5/31/2006 10:00:00 PM      5/31/2006 10:59:00 PM      9
5/31/2006 11:00:00 PM      5/31/2006 11:59:00 PM      7
6/1/2006 12:00:00 AM      6/1/2006 12:59:00 AM      6
6/1/2006 1:00:00 AM                      6/1/2006 1:59:00 AM                      5
6/1/2006 2:00:00 AM                      6/1/2006 2:59:00 AM                      3
6/1/2006 3:00:00 AM                      6/1/2006 3:59:00 AM        3
6/1/2006 4:00:00 AM                      6/1/2006 4:59:00 AM                      4
6/1/2006 5:00:00 AM        6/1/2006 5:59:00 AM        4
6/1/2006 6:00:00 AM        6/1/2006 6:59:00 AM        5
6/1/2006 7:00:00 AM        6/1/2006 7:59:00 AM        6
6/1/2006 8:00:00 AM        6/1/2006 8:59:00 AM        2
6/1/2006 9:00:00 AM        6/1/2006 9:59:00 AM        5
6/1/2006 10:00:00 AM      6/1/2006 10:59:00 AM      8
6/1/2006 11:00:00 AM      6/1/2006 11:59:00 AM      8
6/1/2006 12:00:00 PM      6/1/2006 12:59:00 PM      9  


ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17140509
tscott 72 and GRayL ....

I ran your query against the sample database uploaded for this question and compared the results to the results the function I created and there are some time slots where the count of patients is different.  Example.... Let's take one day/time slot to compare, 05/31/06 8:00:00 AM.  The query returned 5 patients for this time slot and the function I created returned 6 patients.  Below is the detail for the six patients ...

PT#      arrdt                                      depdt
30906721      5/31/2006 6:30:00 AM      5/31/2006 11:15:00 AM
30906739      5/31/2006 6:39:00 AM      5/31/2006 9:00:00 AM
30906747      5/31/2006 6:44:00 AM      5/31/2006 9:47:00 AM
30906754      5/31/2006 7:48:00 AM      5/31/2006 9:10:00 AM
30906762      5/31/2006 7:56:00 AM      5/31/2006 8:40:00 AM
30906770      5/31/2006 8:38:00 AM      5/31/2006 11:25:00 AM

Again, you may want to compare those two solutions real carefully depending on how you want to represent the count of patients.

ET
0
 

Author Comment

by:tscott_72
ID: 17140539
yeah, I'm looking real critically now at the results. Certainly in the case you gave, and several others I'm seeing, the counts to the function are more in line with my intent.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17140633
Just a question, nothing to do with the points or anything, I'm just trying to figure out which solution produced the results you were expecting.  Using the 05/31/2006 date for comparison purposes, please advise which solution is correct.

The CrossTab Query Produced ...

TimeOfDay       06-05-31
1:00:00 AM      1
2:00:00 AM      
3:00:00 AM      1
4:00:00 AM      
5:00:00 AM      
6:00:00 AM      4
7:00:00 AM      6
8:00:00 AM      5
9:00:00 AM      10
10:00:00 AM      12
11:00:00 AM      15
12:00:00 PM      12
1:00:00 PM      10
2:00:00 PM      12
3:00:00 PM      15
4:00:00 PM      14
5:00:00 PM      12
6:00:00 PM      14
7:00:00 PM      7
8:00:00 PM      6
9:00:00 PM      6
10:00:00 PM      7
11:00:00 PM      8


The VB Function produced ....

HOUR_DETAIL                     HOUR_DETAIL2            NUM_OF_PATIENTS
5/31/2006 1:00:00 AM        5/31/2006 1:59:00 AM     1
5/31/2006 2:00:00 AM        5/31/2006 2:59:00 AM     0
5/31/2006 3:00:00 AM        5/31/2006 3:59:00 AM     1
5/31/2006 4:00:00 AM        5/31/2006 4:59:00 AM     0
5/31/2006 5:00:00 AM        5/31/2006 5:59:00 AM     0
5/31/2006 6:00:00 AM        5/31/2006 6:59:00 AM     4
5/31/2006 7:00:00 AM        5/31/2006 7:59:00 AM     6
5/31/2006 8:00:00 AM        5/31/2006 8:59:00 AM     6
5/31/2006 9:00:00 AM        5/31/2006 9:59:00 AM     9
5/31/2006 10:00:00 AM      5/31/2006 10:59:00 AM     13
5/31/2006 11:00:00 AM      5/31/2006 11:59:00 AM     14
5/31/2006 12:00:00 PM      5/31/2006 12:59:00 PM     12
5/31/2006 1:00:00 PM        5/31/2006 1:59:00 PM     11
5/31/2006 2:00:00 PM        5/31/2006 2:59:00 PM     12
5/31/2006 3:00:00 PM        5/31/2006 3:59:00 PM     15
5/31/2006 4:00:00 PM        5/31/2006 4:59:00 PM     14
5/31/2006 5:00:00 PM        5/31/2006 5:59:00 PM     14
5/31/2006 6:00:00 PM        5/31/2006 6:59:00 PM     14
5/31/2006 7:00:00 PM        5/31/2006 7:59:00 PM     6
5/31/2006 8:00:00 PM        5/31/2006 8:59:00 PM     7
5/31/2006 9:00:00 PM        5/31/2006 9:59:00 PM     6
5/31/2006 10:00:00 PM      5/31/2006 10:59:00 PM     9
5/31/2006 11:00:00 PM      5/31/2006 11:59:00 PM     7

Just trying to figure out which one is the accurate count.

ET




0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17140781
Again, I'm not trying to discredit GrayL's solution because I always try to accomplish task like this with SQL first because it's more streamline and easier for the enduser to follow but sometimes you just have to go the VBA route.  This is what Authur Wood was referring to in his comments earlier.

You see, in my solution, once you establish the time slot ranges (8:00:00 AM    8:59:00 AM) which are the Hour_Detail and Hour_Detail2 fields in the temp table, then it's just a matter of counting each patient where he/she's arrival time is less than or equal to 8:59:00 AM  "Hour_Detail2" AND where the departure time is greater than 8:00:00 AM.  That logic is a no miss and will accurately count each patient for each time slot each time.  

It's the long way around but I think it's more accurate looking at how you want to represent the patient counts.  Only you can make that decision.

ET
0
 

Author Comment

by:tscott_72
ID: 17140795
I see the disparities and in each case the function produces the intended result. I suppose that does have something to do with points. I really appreciate the work done here by both you and Grayl. The data comes from our automated systems, not a form. So, form events are not too useful here. Of course, I prefer the simplicity of a straight SQL solution. The function, at least for my purposes, seem to produce better results.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17140971
>>>The data comes from our automated systems, not a form. So, form events are not too useful here.<<<<<

Not sure what you mean here.  You could put the function in a module and call it from anywhere using a macro or in vb code such as ....  Call CountPatients().  As far as setting the dteArrival and dteDepart variables in the function if you are not calling this from a form you could simply fill these using the DMin and DMax values from the table.  I was under the impression that you wanted to run this for a range sounds like your want to run this each time for whatever values exist in the patients table.

Let me know as I can simply modify the function to pick up the min and max values from the arrival time field as opposed to looking for a range input from the user.

ET



0
 

Author Comment

by:tscott_72
ID: 17141055
exactly. The function will be used for monthly stats. The table, called "tbl_los_census", is reproduced monthly with a Make Table query (I did this as a simple workaround for some troublesome IIf statements). So, the table is limited to the range of values already and min max would work great.

All I meant before was that an SQL solution that works the same (not the case here) has a simple elegance that appeals to me, especially because SQL is a comfort zone for me. VBA, while fascinating, is unchartered territory. I enjoy adapting other people's code, but I really don't understand code yet. I am running the function from a module.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17141167
To remove that 5v6 anomaly at 07:00 i changed my sql to:

TRANSFORM Count([PT#])
SELECT TimeSerial([Hrs].[hr],0,0) AS TimeOfDay
FROM admissions, Hrs
WHERE (DateValue(ArrDT)+TimeSerial([Hrs].[hr],0,0)) Between dateadd("n",-minute(ArrDT),ArrDT) and DepDT
AND DepDT Is Not Null
GROUP BY TimeSerial([Hrs].[hr],0,0)
PIVOT Format(ArrDT,"yy-mm-dd");

That clears up the 07:00 problem but at 09:00 I show 10 and ET shows 9.  When I checked the original data, it came to 10.
0
 

Author Comment

by:tscott_72
ID: 17141240
I also show 10 for the 9-9:59 period:

PT#      arrdt                     depdt
30906721      5/31/06 6:30      5/31/06 11:15
30906739      5/31/06 6:39      5/31/06 9:00
30906747      5/31/06 6:44      5/31/06 9:47
30906754      5/31/06 7:48      5/31/06 9:10
30906770      5/31/06 8:38      5/31/06 11:25
30906796      5/31/06 9:02      5/31/06 10:15
30906788      5/31/06 9:05      5/31/06 12:20
30906812      5/31/06 9:32      5/31/06 10:35
30906804      5/31/06 9:38      5/31/06 16:20
30906820      5/31/06 9:40      5/31/06 11:05

I'm getting so confused. Uhg! Thanks.

0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17141259
Here you go tscott 72  ...

I modified the function to basically pick up the min and max values from your admissions table that will be used to build the time slots.  No need for user input, just call the function like   Call CountPatients().  Note, in this code I used your actual table and field names from your sample database.  Also, when finished, this code will open the PatientsTbl_Temp in Datasheet view.  You can take that out if you don't need it.

Here is the modified function.

Function CountPatients()
DoCmd.SetWarnings False
DoCmd.Hourglass True
DoCmd.RunSQL "Delete * From PatientTbl_Temp;"

Dim rst As Recordset
Dim rst2 As Recordset
Dim dteMin As Date
Dim dteMax As Date
Dim dteStart As Date
Dim dteEnd As Date


dteMin = DMin("[arrdt]", "admissions")
dteMax = DMax("[arrdt]", "admissions")

Set rst = CurrentDb.OpenRecordset("admissions", dbOpenDynaset)
Set rst2 = CurrentDb.OpenRecordset("PatientTbl_Temp", dbOpenDynaset)

'Round start time to the whole hour
dteStart = DateAdd("n", -(DatePart("n", dteMin)), dteMin)
dteStart = DateAdd("s", -(DatePart("s", dteStart)), dteStart)

'Round end time to the whole hour
 dteEnd = DateAdd("n", -(DatePart("n", dteMax)), dteMax)
 dteEnd = DateAdd("s", -(DatePart("s", dteEnd)), dteEnd)

If rst.RecordCount > 0 Then

 'Number the temp table with 1 hour increments starting with the min arrival time.
 Do Until dteStart > dteEnd
    rst2.AddNew
    rst2!HOUR_DETAIL = dteStart
    rst2!HOUR_DETAIL2 = DateAdd("n", 59, dteStart)
    rst2.Update
    dteStart = DateAdd("h", 1, dteStart)
 Loop
 
 'Count the number of times each patient falls within the hour time slots.
 rst2.MoveFirst
    Do Until rst2.EOF
        rst2.Edit
        rst2!NUM_OF_PATIENTS = DCount("[PT#]", "admissions", "[arrdt] <= #" & rst2!HOUR_DETAIL2 & "# AND [depdt]> #" & rst2!HOUR_DETAIL & "#")
        rst2.Update
       
        rst2.MoveNext
    Loop
 
 
End If

rst.Close
rst2.Close
   
DoCmd.Hourglass False
DoCmd.OpenTable "PatientTbl_Temp", acViewNormal
End Function



This should get you what you are looking for.

ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17141366
tscott an GrayL ...

The answer to your question regarding the 9:00:00 to 9:59:00 time slot is simple and is why I commented on this in my original post dated 07/18/2006 11:47 PM that tscott accepted as an Assisted answer.

I will explain it again here ...

The code will basically create a time slot range (9:00:00  9:59:00, etc., etc.)  based on the min and max Arrival date field.  Note: Say if a patient departs at "exactly" 9:00:00 AM they will not be counted in the time slot 9:00:00 AM  9:59:00 simply because they are gone/not there.  If a patient arrives at "exactly" 9:59:00 then they will be counted in the time slot 9:00:00 AM  9:59:00 because they are there.  

For this time slot, patient id 30906739 departed at exactly 9:00:00 therefore he should not be counted for that period thus yeilding a patient count of 9 rather than 10.

To me, if a class runs from 8:00 to 9:00 and the student signs out of the class at 8:00 you would not count them as present for the class period.  They are gone and I would think the same applies in the patient count.  

ET

0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17141418
Here is the detail for the 9 patients time slot 9 - 9:59.  Notice the patient that departed at exactly 9:00 is not included.

PT#      arrdt                                      depdt
30906721      5/31/2006 6:30:00 AM      5/31/2006 11:15:00 AM
30906747      5/31/2006 6:44:00 AM      5/31/2006 9:47:00 AM
30906754      5/31/2006 7:48:00 AM      5/31/2006 9:10:00 AM
30906770      5/31/2006 8:38:00 AM      5/31/2006 11:25:00 AM
30906788      5/31/2006 9:05:00 AM      5/31/2006 12:20:00 PM
30906796      5/31/2006 9:02:00 AM      5/31/2006 10:15:00 AM
30906804      5/31/2006 9:38:00 AM      5/31/2006 4:20:00 PM
30906812      5/31/2006 9:32:00 AM      5/31/2006 10:35:00 AM
30906820      5/31/2006 9:40:00 AM      5/31/2006 11:05:00 AM

ET

0
 
LVL 44

Expert Comment

by:GRayL
ID: 17141867
I have to ask why?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17141873
I have to ask why?
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17141980
>>>>I have to ask why?<<<<

Well, look at it this way .....

At 9:00 AM patient A departed the ER and at the same time 9:00 AM patient B arrived at the ER.  At 9:59 how many people were actually in the ER for that time slot???  To me that count should be 1.

It just depends on how tscott wants to represent the count of patients.  I can easily include the patient in that slot but I think that would artificially inflate the count for patients that have already gone for that period.

These types of counts are like sales commissions where every company will do it differently.  Tscott will have to decide how he wants to represent the patient count in cases like this.

ET
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17142064
In what I call the nine o'clock hour which runs from 09:00:00 to 09:59:59.999 I say there are two - but, as you say, it depends on how tscott_72 wants to define the rule.  When all is said and done, I'd rather maintain a query than code.  I think it is also safe to say this can be done with just SQL - contrary to earlier allegations.  Just my 2¢.

Thanks, and good luck with the application.

Ray
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17142137
Yep, GRayL ...

Most people will migrate to whatever they are comfortable with.  I generally start out with SQL then if it looks like I'm not getting the desired results I then go to VBA Code.

This is why I generally like the asker to detail as much as possible about the question because there are so many different approaches you can take where neither is right or wrong.  

Again, kind of like sales commissions, where every company has a different set of rules/cirteria.  This is one of those where you got to be specific.

Later ...

ET
0
 

Author Comment

by:tscott_72
ID: 17142647
This is almost like a cat fight between nurses at my job...fun. I can tell you folks respect each other and that's good. As for my preferences: I just want something that gives me confidence in the accuracy and consistency (i.e. replicability) of the results. Generally speaking, I have to agree with the logic of ET's treatment of the 9:00 counts. It absolutely makes sense to me that a patient who leaves the ED at 9 is not really in present during the 9 o'clock hour. Essentially the same thing can be said of 9:01 or 9:02. The line has to be drawn somewhere for a cutoff. Is 8:59 more reasonable than 9:00 as a cutoff? Ultimately, we are splitting hairs over 1 minute and either treatment is reasonable. I think as long as I am explicit  in my reporting about the counting method and it is consistent, either option is equally acceptable.  
Question: is either approach possible in the straight SQL approach? SQL appeals to me as a first resort, mostly because I am not there yet with VBA. The SQL I can study, then understand and use to improve my own SQL. The VBA, at least for now, will be borrowed code from somebody operating on a higher plane. Once my comfort is increased, I problably won't see VBA as a complex last resort.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17143614
>>>>>Question: is either approach possible in the straight SQL approach? SQL appeals to me as a first resort, mostly because I am not there yet with VBA. The SQL I can study, then understand and use to improve my own SQL. The VBA, at least for now, will be borrowed code from somebody operating on a higher plane. Once my comfort is increased, I problably won't see VBA as a complex last resort.<<<<<<

tscott 72, I totally understand and I'm sure GRayL can tweak the SQL to accomplish the same objective.  You have to go with what you are comfortable with especially in cases where changes down the road might arise although that VBA code is not all that complex.

Again, thanks for the points and I will leave you in the hands of GRayL.

Later ...

ET
0
 

Author Comment

by:tscott_72
ID: 17145953
ET: I intend to study your code and learn from it. VBA is fascinating to me and it's power is undeniable. I appreciate your help and expertise. Thank you.
0
 

Author Comment

by:tscott_72
ID: 17146988
IT LOOKS TO ME THAT THE QUERY, AND NOT THE FUNCTION, SKIPS THE 12 AM - 1 AM PERIOD. I added a zero hour to the hrs table, producing the 12 o'oclock hour in the results and some crazy numbers.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17148683
No problem tscott 72 ...  As mentioned earlier, your request was kind of interesting and challenging so I decided to give it a try.

Also, you did get the latest version of the code that will use the min and max values from your admissions table as well as your actual tables/field names???  This question has had so many responses and is very long now.  

In case you didn't, here it is again ....  "Solid as a rock ..."   :-)

Function CountPatients()
DoCmd.SetWarnings False
DoCmd.Hourglass True
DoCmd.RunSQL "Delete * From PatientTbl_Temp;"

Dim rst As Recordset
Dim rst2 As Recordset
Dim dteMin As Date
Dim dteMax As Date
Dim dteStart As Date
Dim dteEnd As Date


dteMin = DMin("[arrdt]", "admissions")
dteMax = DMax("[arrdt]", "admissions")

Set rst = CurrentDb.OpenRecordset("admissions", dbOpenDynaset)
Set rst2 = CurrentDb.OpenRecordset("PatientTbl_Temp", dbOpenDynaset)

'Round start time to the whole hour
dteStart = DateAdd("n", -(DatePart("n", dteMin)), dteMin)
dteStart = DateAdd("s", -(DatePart("s", dteStart)), dteStart)

'Round end time to the whole hour
 dteEnd = DateAdd("n", -(DatePart("n", dteMax)), dteMax)
 dteEnd = DateAdd("s", -(DatePart("s", dteEnd)), dteEnd)

If rst.RecordCount > 0 Then

 'Number the temp table with 1 hour increments starting with the min arrival time.
 Do Until dteStart > dteEnd
    rst2.AddNew
    rst2!HOUR_DETAIL = dteStart
    rst2!HOUR_DETAIL2 = DateAdd("n", 59, dteStart)
    rst2.Update
    dteStart = DateAdd("h", 1, dteStart)
 Loop
 
 'Count the number of times each patient falls within the hour time slots.
 rst2.MoveFirst
    Do Until rst2.EOF
        rst2.Edit
        rst2!NUM_OF_PATIENTS = DCount("[PT#]", "admissions", "[arrdt] <= #" & rst2!HOUR_DETAIL2 & "# AND [depdt]> #" & rst2!HOUR_DETAIL & "#")
        rst2.Update
       
        rst2.MoveNext
    Loop
 
 
End If

rst.Close
rst2.Close
   
DoCmd.Hourglass False
DoCmd.OpenTable "PatientTbl_Temp", acViewNormal
End Function

0
 

Author Comment

by:tscott_72
ID: 17148742
it is solid and I am using its numbers for a report and presentation for Monday afternoon. I did use the updated code. It populated the table and also crashed my computer repeatedly. When reopening the dbase, I got a message about the dbase needing repair and that code has been run that intentionally seeks to harm my computer. ??? Anyway, once all those shenanighans were done, I was left with an accurate table. Past versions did that too; when the beginning date was set to MM/DD/YYYY 12:00:00 am. Never happened when the date was MM/DD/YYYY 12:00:01 am. qUIRKY.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17148955
tscott 72 ....

Sounds like you got something else going on with your workstation (maybe a virus).  Nothing in that code would produce that type of results.  I can run it against your sample db and it works each time.

Not really sure what you mean by "when the beginning dat was set to mm/dd/yyyy 12:00:00 am".

ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17149055
Also,

Open your Module in Design mode.  Then click  Tools --------> References  and let me know which References are checked on.


ET

0
 

Author Comment

by:tscott_72
ID: 17149221
VBA,11.0 Object Library,OLE Automation,DAO 3.6,ActiveX objects 2.1
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 17149334
The only difference is I'm showing 9.0 Object Library.  You also may want to check on  Microsoft Visual Basic for Applications Extensibility 5.3.

ET

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now