Eric-
(Apologies for calling you "Barry" earlier - that's what is in your email line.)
You show these columns:
Nursing Wing
Start Date
Start Time
End Date
End Time
Minutes Between Times
I assume there's also something like a PatientID to define the person involved. Your code might look something like:
Dim db As DAO.Database, rstI As DAO.Recordset, rstO As DAO.Recordset
Dim lngPatientID As Long, strNursingWing As String
Dim datStartD As Date, datStartT As Date
Dim datEndD As Date, datEndT As Date, dblMinutes As Double
' Point to this database
Set db = CurrentDb
' Open the input recordset
Set rstI As db.OpenRecordset("SELECT * FROM BadTimeTable " & _
"ORDER BY PatientID, [Start Date], [Start Time]")
' Open the output recordset
Set rstO = db.OpenRecordset("SELECT * FROM NewTimeTable", _
dbOpenDynaset, dbAppendOnly)
' Save the first record values
lngPatientID = rstI!PatientID
strNursingWing = rst![Nursing Wing]
datStartD = rstI![Start Date]
datStartT = rstI![Start Time]
datEndD = rstI![End Date]
datEndT = rstI![End Time]
' Move to next record and start comparison
rstI.MoveNext
' Loop until done
Do Until rstI.EOF
' If not the same patient and wing,
If (rstI!PatientID <> lngPatientID) Or _
(rstI![Nursing Wing] <> strNursingWing) Then
' Spit out the previous record
rstO.AddNew
rstO!PatientID = lngPatientID
rstO![Nursing Wing] = strNursingWing
rstO![Start Date] = datStartD
rstO![Start Time] = datStartT
rstO![End Date] = datEndD
rstO![End Time] = datEndT
' Calculate the new elapsed time
dblMinutes = ((datEndD + datEndT) - (datStartD + datStartT)) * 1440
rstO![Minutes Between Times] = dblMinutes
rstO.Update
' Save the new values
lngPatientID = rstI!PatientID
strNursingWing = rst![Nursing Wing]
datStartD = rstI![Start Date]
datStartT = rstI![Start Time]
datEndD = rstI![End Date]
datEndT = rstI![End Time]
Else
' Same patient and wing - update the end values
datEndD = rstI![End Date]
datEndT = rstI![End Time]
End If
' Get the next one
rstI.MoveNext
Loop
' Write out the last record saved in memory
rstO.AddNew
rstO!PatientID = lngPatientID
rstO![Nursing Wing] = strNursingWing
rstO![Start Date] = datStartD
rstO![Start Time] = datStartT
rstO![End Date] = datEndD
rstO![End Time] = datEndT
' Calculate the new elapsed time
dblMinutes = ((datEndD + datEndT) - (datStartD + datStartT)) * 1440
rstO![Minutes Between Times] = dblMinutes
rstO.Update
' Done
rstO.Close
rstI.Close
Set rstO = Nothing
Set rstI = Nothing
Set db = Nothing
The above code assumes that for any record End Date and End Time are always later in time than the start date and time. It also ignores any gaps or overlaps. If the patient is in the same place consecutively, then it calculates the time as the End of the last record minus the Start of the first one.
Of course, you'll have to supply the table names and correct any assumptions I've made about the field names.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Barry White
Sent: Friday, February 08, 2013 11:48 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Patient's Intraday Perspective File Problem
If I could get some sample code to get me started that would be great.
Since you are going to bed, and if you already have something drawn up, I can easily wait till your tomorrow.
Thank you, Go to bed, goodnight.
Eric Lutz
________________________________
From: John Viescas JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, February 8, 2013 5:14 PM
Subject: RE: [MS_AccessPros] Patient's Intraday Perspective File Problem
Barry-
That's exactly what I thought you were trying to explain. My original advice stands. To do this in code, you would open a recordset on the table by patient and read the first record into a buffer. Read the next record, and if it's for the same patient and location, adjust the end time in the buffer. If the patient or location has changed, write out the buffer and save the record just read. At the end of the process, write out the last saved record.
Do you need sample code? It's late here, and I'm about to go crash for the night.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: mailto:MS_Access_Professionals%40yahoogroups.com [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Barry White
Sent: Friday, February 08, 2013 10:42 PM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: Re: [MS_AccessPros] Patient's Intraday Perspective File Problem
Thank you. I uploaded the file, which is essentially nothing more than a Word doc with all my verbage and visual example.
It is called PatientIntradayPathwayExample.doc
Eric Lutz
________________________________
From: John Viescas mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Friday, February 8, 2013 4:29 PM
Subject: RE: [MS_AccessPros] Patient's Intraday Perspective File Problem
http://tech.groups.yahoo.com/group/MS_Access_Professionals/files/2_AssistanceNeeded/
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: mailto:MS_Access_Professionals%40yahoogroups.com [mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Barry White
Sent: Friday, February 08, 2013 10:22 PM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Subject: Re: [MS_AccessPros] Patient's Intraday Perspective File Problem
Where is this Files: 2_AssitanceNeeded folder? I do not see it.
Eric Lutz
________________________________
From: John Viescas mailto:JohnV%40msn.com>
To: mailto:MS_Access_Professionals%40yahoogroups.com
Sent: Friday, February 8, 2013 4:08 PM
Subject: RE: [MS_AccessPros] Patient's Intraday Perspective File Problem
Barry-
Sadly, this forum doesn't support highlighting and it doesn't return table data in a very readable form. Perhaps you can upload a couple of pictures of your data to the Files: 2_AssitanceNeeded folder?
Having said that, you might be able to write some code that goes through the entries for each patient and "collapses" them by holding the previous record in memory and simply updating that record if the location and time overlap.
Write out the in-memory record only when you encounter a different location or the end of the set of records.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: mailto:MS_Access_Professionals%40yahoogroups.com
[mailto:mailto:MS_Access_Professionals%40yahoogroups.com] On Behalf Of Barry White
Sent: Friday, February 08, 2013 9:53 PM
To: mailto:MS_Access_Professionals%40yahoogroups.com
Cc: mailto:imtigerwords%40yahoo.com
Subject: [MS_AccessPros] Patient's Intraday Perspective File Problem
Hello all,
Looking for a little help as to how to combat the three dysfunctions in this table (don't roll your eyes at me, I didn't make this mess, I am just trying to clean it up)
The three main dysfunctions in this Patient Intraday Table View are,
1. I need to nix the duplicate or overlapping minutes. This table should show unduplicated time stamps and nursing floors where the patient is staying in a bed. As the patient gets moved around to different parts of the hospital, you should see no gaps in the time and date stamps, when the patient is entering or leaving from one nursing floor locale to another.
2. This whole file needs to be compressed to its most efficient list. Many times I see the same nursing floor repeated back to back to back, where one row of data will do, ultimately displaying a single entry time and date, and a single exit time and date
3. Given this list of inefficiency and duplication, sometimes there is more than one viable path that can be taken to arrive and the correct and unduplicated minutes of total time stayed within the hospital walls.
A fine example is below. This made up patient ultimately stayed in the hospital for 25.12 days, from 12/16/2011 @ 3:21PM to 1/7/2012 @ 1:28PM, or
36,177 total minutes. As you can see from this snippet below there was
39,420 duplicate minutes mixed in, that need to be weeded out.
I need to turn this gobbleygook entirety, Nursing Wing Start Date Start Time End Date End Time Minutes between Times Emergency Room 12/16/2011 3:21 PM 12/16/2011 3:33 PM 12 Holding Area
12/16/2011 3:21 PM 12/16/2011 6:00 PM 159 Holding Area 12/16/2011 6:00 PM
12/16/2011 6:51 PM 51 5th Floor South 12/16/2011 6:51 PM 12/16/2011 11:43 PM
292 Cancer Floor North 12/16/2011 11:43 PM 12/18/2011 1:14 PM 2,251 Cancer Floor North 12/18/2011 1:14 PM 12/23/2011 5:52 AM 6,758 Intensive Care East
12/23/2011 5:52 AM 12/23/2011 6:08 AM 16 Intensive Care East 12/23/2011 6:08 AM 12/23/2011 6:39 AM 31 Intensive Care East 12/23/2011 6:39 AM 12/23/2011
6:40 AM 1 Intensive Care East 12/23/2011 6:39 AM 12/24/2011 12:00 PM 1,761 Intensive Care East 12/23/2011 6:39 AM 12/25/2011 1:56 AM 2,597 Intensive Care East 12/23/2011 6:40 AM 12/24/2011 1:56 AM 1,156 Intensive Care East
12/24/2011 1:56 AM 12/25/2011 1:56 AM 1,440 Intensive Care East 12/24/2011
12:00 PM 12/25/2011 1:56 AM 836 Cancer Floor North 12/25/2011 1:56 AM
12/29/2011 12:00 PM 12,728 Cancer Floor North 12/25/2011 1:56 AM 01/05/2012
11:03 PM 17,107 Cancer Floor North 12/29/2011 12:00 PM 01/05/2012 11:03 PM
21,486 General Med 3rd Floor 01/05/2012 11:03 PM 01/07/2012 1:28 PM 6,915
Into this efficient path,
Nursing Wing Start Date Start Time End Date End Time Minutes between Times Holding Area 12/16/2011 3:21 PM 12/16/2011 6:51 PM 210 5th Floor South
12/16/2011 6:51 PM 12/16/2011 11:43 PM 292 Cancer Floor North 12/16/2011
11:43 PM 12/23/2011 5:52 AM 9009 Intensive Care East 12/23/2011 5:52 AM
12/25/2011 1:56 AM 2644 Cancer Floor North 12/25/2011 1:56 AM 01/05/2012
11:03 PM 17,107 General Med 3rd Floor 01/05/2012 11:03 PM 01/07/2012 1:28 PM
6,915
The highlighted yellow portion from the first piece contains the non-duplicate trip through the hospital as it is displayed in the second piece concisely.
Thank you for any help that can be provided, as this is making my head spin. Imagine a plethora of 200,000 patients in a years time, all ridiculously logged just like this.
I would gladly take either Excel or Access help on this. Any suggestion would do at this point.
Sincerely,
Eric Lutz
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (12) |
Tidak ada komentar:
Posting Komentar