Create a record for top of the hour

Why don't you just record the arrival and departure. The hours in between can be derived from a query or code. One of the principle of database base design is to record only the primary information. Anything that can be derieved from this primary information need not be recorded

because the'developers' in today's IT field did not go to school and learn about relational database structure and design, and normalization, and the thought of all those JOIN statements scares them. They grew up as telephone operators and car mechanics and gardners who thought comuters looked cool, and IT was at a desperate crossroads for manpower, so if you could spell prowgrmur, you got to be one

Create your table as tblPatientHours with CSN as a Long and Date as date/time.
Copy and paste the function below into a (new) module.
Run the function like this:

PatientHours 1234, #10/20/09 05:30#, #10/20/09 10:10#

Make sure a Reference is set for DAO (Menu Tools, References).

/gustav

Public Function PatientHours(ByVal CSN As Long, Arrival As Date, Departure As Date)
 
  Dim dbs     As DAO.Database
  Dim rst     As DAO.Recordset
  Dim intHour As Integer
  Dim intAdd  As Integer
  Dim datDate As Date
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Select * From tblPatientHours")
  
  ' Round times.
  intAdd = Sgn(Minute(Arrival) + Second(Arrival))
  Arrival = DateSerial(Year(Arrival), Month(Arrival), Day(Arrival)) + TimeSerial(Hour(Arrival) + intAdd, 0, 0)
  Departure = DateSerial(Year(Departure), Month(Departure), Day(Departure)) + TimeSerial(Hour(Departure), 0, 0)
    
  
  With rst
    For intHour = 0 To DateDiff("h", Arrival, Departure)
      datDate = DateAdd("h", intHour, Arrival)
      .AddNew
        !CSN.value = CSN
        !Date.value = datDate
      .Update
    Next
    .Close
  End With
  
  Set rst = Nothing
  Set dbs = Nothing
  
End Function

Select allOpen in new window

There might be a need for separate hourly records if, for example, certain tests or readings are recorded on an hourly basis.

To cactus_data:  I will use your instructions today and will let you know how it works


To dbbishop: and sb9:

I am a nurse analyst, not a programmer.  However, I have done my homework, enough to understand that normally, one does not strive to store derived data. I did not seek out IT as a way to escape being a gardener. Actually, some days, I dream of being a gardener.    I jumped into data analysis because I think data is the key to better delivery of healthcare and there weren't many programmers who understood the language of nurses and doctors.  I'm trying with some success to bridge that gap.

When I get beyond my skills, I use make tables.  It is not elegant, but it leaves me with data I can easily validate and build multiple reports from.

I greatly appreciate the experts here who help me.  

Nothing wrong with temp tables or tables only for use by reports or analasys.

/gustav

To cactus_data:

I have my empty table created as you suggest:   tblPatientHours with CSN as a Long and Date as date/time.
I pasted the function into a new module called PatientHours and I added Microsoft DAO 3.6 as a Reference.

Now I have my source data in a table called CSN ArrvDep, that looks like this

CSN                      Arrival                              Departure
165080          8/1/2009 7:51:00 AM      8/1/2009 10:58:00 AM
165081          8/1/2009 7:57:00 AM      8/1/2009 8:31:00 AM

What is the next step?  How do I call your function to add records to the tblPatientHours ?

matillma: Please allow me to apologize. I meant you no disrespect. Although no excuse, I think part of my comment may have come from the fact that I have been fighting a severe sinus infection for almost theree weeks. I wasn't thinking. Sorry.

Most of us are here to help others, share our knowledge and learn as we go along. Every day is a learning experience. I am certain, without doubt, that I have asked question myself that seem so obvious to others, they must have wondered if I was a former plumber who decided to become a programmer. Actually I was an electrician :-)

dbbishop:

No offense taken. I just wanted to explain why, although I have years of experience, there are gaps in my knowledge.  Joins don't scare me but programming surely does.  I'm so close to nailing this thing.  My goal is to analyze patient density and outcomes by hour of day and day of week.  Looking at arrivals and discharges just doesn't get to the point.  

If you haven't tried saline (salt water) irrigation of your sinuses, you might want to check it out.  It makes a huge difference, if you are brave enough to do it.  It really does not hurt.

Hi

WOW! plumber,nurse,electrician,gardender. What does it matters. I am an Environmental Health Officer and like Matillma I got into database to assist with my workload. The bottom line is that database and programming is all about the ability to think systematically and clearly understand the process that you are recording.

Matillma,

I assumed that you were new at database and was providing advise aimed at making things easier. I still stand by my statement that you'd be better off just storing the start and finish time. I think that it would make things so much easier if you latter decide to use the database for other purpose.

 I  believe that expert should providing advise on alternative approaches that may simplify things or are more in line with standard practise rather than just simply tell the author how to do the task they have asked about. That's why I made that comment.

May I ask what do you want to do with the hourly data.

If I understan cactus correctly the patient hour table will just be a temporary table

You can create a form based on your Main table. The one with only arrival and departure

On this form you can have a button. Set the onclick procedure of that button to:

PatientHours (Me.CSN, Me.Arrival,  Me.Departure)

This will call the code when you click the button

You can create a simple query which - when run - will call the function for each record, thus creating the records in the other table:

SELECT
  tblPatients.CSN,
  PatientHours([CSN],[Arrival],[Departure])
FROM tblPatients;

To prevent duplicate records to be created, you may wish to expand the query like this:

SELECT
  tblPatients.CSN,
  PatientHours([tblPatients]![CSN],[Arrival],[Departure])
FROM
  tblPatients
  LEFT JOIN
  tblPatientHours
    ON tblPatients.CSN = tblPatientHours.CSN
WHERE
  tblPatientHours.CSN Is Null;

/gustav

Sorry for delay in responding- I was out for a few days.

I tried both the form command button suggestion and the query/SQL method of calling the function PatientHours and both failed, with an error about undefined function PatientHours in expression.  

I started a new database, added DAO 3.6 Library- I had to deselect MS Access 12.0 Database Engine Object library because I got duplicate name errors.  I have tried re-copying everything.  

So close, yet still eluding me.  Is there a way I could email someone my database with some anonymized data?  

To sb9:

I plan to use hourly data for several things- first, I need to be able to count patients present in the by hour.  Then I need to count different types of patients by hour (children, the elderly, admitted pts, discharged pts, as well as other characteristics).

I need to be able to recreate what was going on in the department at certain points in time.  Having the patient identifier and the top of the hour as a record gives me lots of flexibility.  

With a previous electronic medical record, I had a little database that created these records along with the room the patient was in.  It was wonderful.  I built dozens of useful reports out of those tables.  But it was created in the mid 1990s by another facility.  When we switched to a larger institutional electronic record, my little database wouldn't work.  So for several years I've been limping along without it.  

That's why I want to use make table queries.  I'm trying to get back to something I use to have.

> .. with an error about undefined function PatientHours in expression.

You need to create the function as well:

https://www.experts-exchange.com/questions/24833368/Create-a-record-for-top-of-the-hour.html?cid=1572&anchorAnswerId=25631739#a25631739

/gustav

Matilla

Check the attached sample. I have created what I believe would capture the basic of your requirement.

Have a look at the query which addresses:

<I need to be able to count patients present in the by hour>

Different variations of this query can be used to generate all the requirements that you mentioned in your last post. All you need to do is change the criteria in the DCounts and there you have it.

If you insist on having temporary tables you can use the query to create your table but I would not bother.

You are welcome to post a sample of your DB

The sequal for the query is as follows:

SELECT DCount("[tblAttendance]![fldAttendanceId]","[tblAttendance]",
"[tblAttendance]![fldArrival] <   #8 AM#  AND  [tblAttendance]![fldDeparture] > #7 am# ") AS [7-8],  
DCount("[tblAttendance]![fldAttendanceId]","[tblAttendance]",
"[tblAttendance]![fldArrival] <   #9 AM#  AND  [tblAttendance]![fldDeparture] > #8 am# ") AS [8-9],  
DCount("[tblAttendance]![fldAttendanceId]","[tblAttendance]"
,"[tblAttendance]![fldArrival] <#10AM#  AND  [tblAttendance]![fldDeparture] > #9 am# ") AS [9-10],  
DCount("[tblAttendance]![fldAttendanceId]","[tblAttendance]"
,"[tblAttendance]![fldArrival] <#11AM#  AND  [tblAttendance]![fldDeparture] > #10 am# ") AS [10-11],  
DCount("[tblAttendance]![fldAttendanceId]","[tblAttendance]"
,"[tblAttendance]![fldArrival] <#12PM#  AND  [tblAttendance]![fldDeparture] > #11 am# ") AS [11-12],  
DCount("[tblAttendance]![fldAttendanceId]","[tblAttendance]"
,"[tblAttendance]![fldArrival] <#1PM#  AND  [tblAttendance]![fldDeparture] > #12PM# ") AS [12-1],  
DCount("[tblAttendance]![fldAttendanceId]","[tblAttendance]"
,"[tblAttendance]![fldArrival] <#2PM#  AND  [tblAttendance]![fldDeparture] > #1PM# ") AS [1-2];

Select allOpen in new window

dbClinic.mdb

Also

You can use DLookup to find which patient was there at a particular time interval.

As I said initially just record the basic info patient, clinic, arrival,departure, admission and let the query do the hard work.

Also

The table patient will obviously have the patient date of birth & gender, query can be used to determine age group.

To cactus_data:
I have attached my mdb with sample data.  I did create the function as directed

To sb9:  Thanks for the DLookup suggestion.  I am studying your database to figure out how to apply your structure to my data

I prefer to have more than one way to arrive at answers- it helps me validate my results
Census.mdb

Rename the module to, say, basPatientHours

/gustav

You need to change the format of the date field in the table too.

/gustav

To cactus_data:

Ok, renaming the module fixed the function error and I changed the date format.  I was able to return data, with no error messages, using the simple query you suggested.  
The data appears accurate, for records it created.  
However it only returned 159 records all dated 8/1/09.  My table has 8600+ cases dated 8/1 to 10/20, so I should have well over 30K records (since the average pt stay is over 4 hrs).  

I do not find any kind of date range restriction in the code or SQL.  Why would the code only run through the first 40 or so of cases and stop without error?

To cactus_data:

Ok I ran it again and got 3500 records that look about right.  Still too few, but way more than 159 records.    Am I interrupting it by opening the table too soon?  If so, how do I know when it's done?  Normally MS Access won't let me do anything while a query is running.

ok,sb9

I'm trying out DCount but I have a syntax error or something

Hour01: DCount("[tblPatients]![CSN]","[tblPatients]","[tblPatients]![Arrival] <   #2 AM#  AND  [tblPatients]![Departure] > #1 AM# ")

returns a count of 0.  There are currently over 9000 cases in my table and 151 of them arrived between 0100 and 0200, so 0 is incorrect

to sb9:
I don't think DCount will work right on my data.  ED patients arrive 24 hours a day and stay in the ED anywhere from minutes to days.  Arrival is a general date field (8/01/09 0130), not a simple time field as your sample database.  I need to be able to count patients who arrived on previous calendar days if they have not yet departed.

I tried rewriting your DCount statement but I am not seeing a way to make it capture all the patients.

matillma: Can you mick up a sample output that you would like to see from this query?

Is the idea to provide only a report (hardcopy) that has additional 'hourly' lines for making manual notes, or to actually provide data entry lines into your system on these hourly basis?

Something rough, laid out in Notepad or even a screenshot would help? I have something that might be somehat 'similar' I use in SQL Server that may be of assistance.

mick up = 'mock up'

to cactus_data:

That seems to have done the trick- it now returns over 50k records.  I need to do some validating, but that is what I would expect for 9000 pts staying an average of 5+ hrs.  I thank you so much for guiding me through this.  As soon as I validate, I will accept solution.  

One other question, tho.  My next project is adding the room location for each pt at the top of each hour.  I am hoping to modify your code to do that.  The time in room is easy.  The time out of room is either departure time or the next time in a room that is not the current room.  Will your code be adaptable to accomplishing this?

Probably. But I can't imagine where to input that data. Most likely you would need to provide some example data.

/gustav

I truly appreciate the help!  This is a longstanding problem and it is a real relief to have a solution

You are welcome!

/gustav