Eric,
Apparently, for each admit date for a given patient, you wish to get the running total of days elapsed since the first discharge date.
So long as you have the patient ID for each record, the desired objective could be realized without needing the extra two columns (holding arbitrary values like "999999") in your source table.
Let the source table T_PtVisits have fields PtID, DtAdmit and DtDisch (last two being of date type). Sample query Q_DaysSinceFirstDisch as given below, should get you he desired results. Calculated field DaysSinceFirstDisch shows the number of days elapsed between the first discharge date and the current admit date. This query makes use of totals query Q_DtFirstDisch (also given below), which identifies the date of first discharge for each patient.
Best wishes,
A.D. Tejpal
------------
Q_DaysSinceFirstDisch (Final Query)
(Uses stage-1 query Q_DtFirstDisch given below)
===================================
SELECT T_PtVisits.*, IIf([DtAdmit]-[DtFirstDisch]>=0,[DtAdmit]-[DtFirstDisch],Null) AS DaysSinceFirstDisch
FROM T_PtVisits INNER JOIN Q_DtFirstDisch ON T_PtVisits.PtID = Q_DtFirstDisch.PtID;
===================================
Q_DtFirstDisch (Stage-1 Query)
(Feeds final query Q_DaysSinceFirstDisch given above)
===================================
SELECT T_PtVisits.PtID, Min(T_PtVisits.DtDisch) AS DtFirstDisch
FROM T_PtVisits
GROUP BY T_PtVisits.PtID;
===================================
----- Original Message -----
From: Barry White
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, June 01, 2011 22:31
Subject: Re: [MS_AccessPros] Trying to automate multiple patients "chains" of hospital visits
Okay I uploaded the file, its a word doc really, labeled PatientVisits.doc
I suppose what I am really looking for is how to automate a cummulative days function, where a patient can have an open ended number of visits, put in chronological order by patient number, admit date, and visit number.
There is an initial visit by the patient, then all subsequent visits.
The second visit's admit date minus the initial visit's discharge date, would be the cummulative days at that point, then, all subsequent visits would be the admit date minus the initial visits discharge date.
Eric Lutz
--- On Wed, 6/1/11, Bill Mosca <wrmosca@comcast.net> wrote:
From: Bill Mosca <wrmosca@comcast.net>
Subject: Re: [MS_AccessPros] Trying to automate multiple patients "chains" of hospital visits
To: MS_Access_Professionals@yahoogroups.com
Date: Wednesday, June 1, 2011, 11:24 AM
Andy, Eric
It's HIPAA, not HIPPA <s>
It stands for "Health Insurance Portability and Accountability Act"
Bill
--- In MS_Access_Professionals@yahoogroups.com, "Andrew Mills" <amills@...> wrote:
>
> Eric:
>
> You can't attach a file to the email. You have to upload it to the Group's Yahoo page in the "Needs Assistance" folder.
>
> I have to say though: I am very leery that an Access database is an acceptable database for medical and patient records. I would think this is a serious violation of HIPPA rules due to the very lax security surrounding an Access database.
>
> I know an Access database would never be allowed for Credit Cards (or processing those like Ebay, Amazon, or any other application that accepts credit cards as payment) due to PCI regulations. When you get hacked the first month in business due to this, your company wouldn't be around much longer.
>
> I would have to think HIPPA wouldn't allow such a database to store such confidential information.
>
> Have said that, I do not know for certain, but it might be worth the effort to find out.
>
> Thanks
>
> Andy
>
>
> From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Barry White
> Sent: Wednesday, June 01, 2011 10:03 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Trying to automate multiple patients "chains" of hospital visits
>
> Okay I give up.
>
> How do you attach a document to this group, so it comes out in the correct readable format?
>
> It did not take my word doc attachment and when I simply do a paste as in below it comes out all nasty.
>
> Eric Lutz
>
> --- On Wed, 6/1/11, Barry White <imtigerwords@... <mailto:imtigerwords%40yahoo.com> > wrote:
>
> From: Barry White <imtigerwords@... <mailto:imtigerwords%40yahoo.com> >
> Subject: Re: [MS_AccessPros] Trying to automate multiple patients "chains" of hospital visits
> To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
> Date: Wednesday, June 1, 2011, 9:59 AM
>
> Hello all,
>
> I am viewing multiple patient's medical data.
>
> The very first visit by that patient over a given timeframe is designated with "999999"
>
> For each subsequent visit by that same patient I wish to calculate a running total of days since the discharge date of the initial visit. The date of discharge is the second column of dates in the set below. The first column of dates are the admit dates.
>
> A new patient's initial visit begins, when you run into another set of "999999"
> (I am not particular as to what value I give the initial visit, it could be 999999 or any value as long as its consistent).
>
>
> So in row 2, with the admit date being 2/23/2010 and the discharge of 2/27/2010, the days since the initial visit is 5, as seen in column 3, and it is calculated as
>
> 2/23/2010 minus 2/18/2010
>
> The third visit by this same patient is an admit date of 3/6/2010 and discharge of 3/10/2010
>
> So you could either take 3/6/2010 minus 2/18/2010 for the result of 16, OR you could do it the way I have done it below, which is to take 3/6/2010 minus 2/23/2010 = 11 (subtracting admit dates), and add the 11 from the 3rd row of the fourth column to the 5 from the second row in the third column.
>
> That 5 + 11 = 16, the 16 will need to be added to the 56, yielding 72 as a running total. The 72 will need to be added to the 177 to equal 249, the 249 in turn added to the 45 to ultimately give 294 days as the total days from the first visits day of discharge.
>
> The problem comes in because each patient can have a unique and totally different number of visits, within a given time frame, so you never know when the "chain" will end.
>
> Sometimes it ends with two visits only, sometimes 10, or 3, or 5, or 7, etc.
>
>
>
>
> 02/16/2010
>
> 02/18/2010
>
> 999999
>
> 999999
>
> 02/23/2010
>
> 02/27/2010
>
> 5
>
> 7
>
> 03/06/2010
>
> 03/10/2010
>
> 7
>
> 11
>
> 05/01/2010
>
> 05/03/2010
>
> 52
>
> 56
>
> 10/25/2010
>
> 10/27/2010
>
> 175
>
> 177
>
> 12/09/2010
>
> 12/16/2010
>
> 43
>
> 45
>
> 12/28/2009
>
> 01/01/2010
>
> 999999
>
> 999999
>
> 01/28/2010
>
> 02/09/2010
>
> 27
>
> 31
>
> 06/05/2010
>
> 06/10/2010
>
> 116
>
> 128
>
> 08/16/2010
>
> 08/20/2010
>
> 67
>
> 72
>
> 10/04/2010
>
> 10/07/2010
>
> 45
>
> 49
>
> 02/21/2010
>
> 02/23/2010
>
> 999999
>
> 999999
>
> 01/21/2011
>
> 01/29/2011
>
> 332
>
> 334
>
> 06/30/2010
>
> 07/02/2010
>
> 999999
>
> 999999
>
> 01/01/2011
>
> 01/04/2011
>
> 183
>
> 185
>
>
>
> --- On Wed, 6/1/11, Barry White <imtigerwords@... <mailto:imtigerwords%40yahoo.com> > wrote:
>
> From: Barry White <imtigerwords@... <mailto:imtigerwords%40yahoo.com> >
> Subject: [MS_AccessPros] Trying to automate multiple patients "chains" of hospital visits
> To: MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals%40yahoogroups.com>
> Date: Wednesday, June 1, 2011, 9:56 AM
>
> Hello all,
>
> I would appreciate a point in the right direction, or a little assist with the attached.
>
> Thank you in advance for all your help.
>
> Eric Lutz
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar