Results 1 to 10 of 10
  1. #1
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169

    Query Confusion

    I have a bit of a complex problem. I have a table that contains hospital admissions, skilled nursing admissions and hospice admissions.
    There are also admission dates and discharge dates and the member patient number. There are 125715 rows that are not distinct. The table looks similiar to this.

    id admit disch srvc
    111 8/13/2010 8/18/2010 hospital


    111 8/18/2010 8/20/2010 skilled nursing
    111 8/20/2010 8/30/2010 hospice
    222 7/1/2010 7/15/2010 hospital
    333 9/19/2011 9/22/2011 hospital
    333 9/26/2011 9/29/2011 hospital
    333 9/29/2011 10/6/2011 skilled nursing

    I need to separate out those that are really hospital such as id 222 and the 1 id 333 because the one that is 333 was discharged 9/22/2011 and the next admission is not until 9/26/2011. The ones that are skilled nursing but also hospital need to be in another table that have a disch date the same as admission date. So I should end up with 2 tables that look like this.

    id admit disch srvc
    222 7/2/2010 7/15/2010 hospital
    333 9/19/2011 9/22/2011 hospital

    the 2nd table

    id admit disch srvc
    111 8/13/2010 8/18/2010 hospital
    111 8/18/2010 8/20/2010 skilled nursing
    111 8/20/2010 8/30/2010 hospice
    333 9/26/2011 9/29/2011 hospital
    333 9/29/2011 10/6/2011 skilled nursing

    Just not sure how to query this to make it work.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You do not need to separate the data into distinct tables, that will only complicate things. Your table is structured properly. You just need the correct query to extract the data you need.

    First, you should create a query that extracts those admissions that have a srvc="hospital"


    SELECT id, admit, disch, srvc
    FROM yourtable
    WHERE srvc="hospital"

    From there we need to weed out the records that have an admission date equal to the disch date for a particular patient but where srvc<>"hospital". Now does it matter what the srvc is on the adjacent admission. You only mentioned skilled nursing admissions that were coincident with with a hospital discharge. Did you also need to weed out where a hospital discharge date was the same as a hospice admission?

  3. #3
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    Not exactly. What I need is anything that is other than hospital like where there is a skilled nursing or hospice, that hospital stay needs to be along with the skilled nursing or hospice. So If I have

    id admit disch srvc
    111 8/13/2010 8/18/2010 hospital
    111 8/18/2010 8/20/2010 skilled nursing
    111 8/20/2010 8/30/2010 hospice
    222 7/1/2010 7/15/2010 hospital
    333 9/19/2011 9/22/2011 hospital
    333 9/26/2011 9/29/2011 hospital
    333 9/29/2011 10/6/2011 skilled nursing

    then the only records I need for my hospital query are the ones that have no hospice or skilled nursing. So I only want ID 222 and then the 1 ID 333 that is a hospital admit 9/19 and disch 9/22.

    All other records that are hospital, skilled nursing, hospice will be in a separate pull and all considered something other than hospital. This is because the disch date for instance on 111 that is 8/18 then has an admit to skilled nursing of 8/18 and same with the hospice. All those need to be in their own thing. There has to be a query for this because I sure as heck do not want to take 125k plus records and manually change the first line to skilled nursing since it coordinates to that so that I can end up just doing a query at the end that is =hospital and 2nd query <>hospital. I have to do it this way because I have a program of odds ratio I am using and if I just say give me all hospital those that went to hospice or skilled during the same time period or overlapping get counted 2 times. 1 time in hospital and 1 time in the skilled nursing which is not correct. if they have any item in their record other than a hospital only then they are counted as a skilled nursing or something not hospital.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I think I understand what you are saying.

    Continuing with the query. We can start with this basic query to just isolate all records where srvc=hospital


    SELECT id, admit, disch, srvc
    FROM yourtable
    WHERE srvc="hospital"

    Now, we need to find out if there is an admission (for something other than hospital) on the same date as the hospital discharge. For that we need a subquery, so now our query looks like this (subquery shown in red):


    SELECT myTable.ID, myTable.admit, myTable.disch, myTable.srvc, (SELECT top 1 Q1.admit FROM myTable as Q1 WHERE srvc<>"hospital" and q1.admit=mytable.disch and Q1.ID=myTable.ID) as OtherAdmission
    FROM myTable
    WHERE (((myTable.srvc)="hospital"));

    So if a record shows an admit date in the OtherAdmission field then that record would not count as a hospital admission. Using your data presented earlier, the query above will yield the following:


    ID admit disch srvc OtherAdmission
    111 8/13/2010 8/18/2010 hospital 8/18/2010
    222 7/1/2010 7/15/2010 hospital
    333 9/19/2011 9/22/2011 hospital
    333 9/26/2011 9/29/2011 hospital 9/29/2011


    Now we can create a query based on the above query that ignores those records with a date in the OtherAdmission field to get the hospital data you are after. That query would look like this

    SELECT qryRawHospitalData.ID, qryRawHospitalData.admit, qryRawHospitalData.disch, qryRawHospitalData.srvc
    FROM qryRawHospitalData
    WHERE (((qryRawHospitalData.OtherAdmission) Is Null));

    I've attached an example database that includes the above queries.
    Attached Files Attached Files

  5. #5
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    what does the select top 1 in the subquery mean? can I leave that out if I have more than one admissions other than an inpatient? Meaning if it is like

    111 1/1/2010 1/5/2010 hospital
    111 1/5/2010 1/15/2010 nurse
    111 1/15/2010 1/20/2020 rehab

    So this means there are 2 others associated with the hospital. So I would think I do not need the top 1. Not sure what the top 1 is doing.

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    A subquery within a SELECT clause can only return 1 record. So if there just happened to be both a skilled nursing and a hospice admission with the same admission date as the hospital discharge date that would cause a problem since 2 records could be returned. From what you said, you only cared whether any non-hospital admission occurred on the hospital discharge date. If you take out the Top 1, the query will error out (if 2 non-hospital events occurred on the discharge date for a patient).

    111 1/1/2010 1/5/2010 hospital
    111 1/5/2010 1/15/2010 nurse
    111 1/15/2010 1/20/2020 rehab
    What you describe above is different from what you described earlier. You are basically saying that you may have several cascading events related to a hospital stay (the rehab admission event does not start on the hospital discharge date but rather depends on the intermediate nursing event). I think that may require a different table structure but without knowing more about your application that is hard to say.

  7. #7
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    I am just throwing other things into the mix because there are 125k+ records and to determine what all is in here I would have to go thru all of them.

  8. #8
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    The table structure is set as it is because it comes from our claims database. So I cannot change the way it appears. I guess I have to go through all 125k+ rows and see if there is anything other than 1 other associated with a hospital. If so I will get back to you.

  9. #9
    tmcrouse is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Apr 2009
    Posts
    169
    Ok just ran into one like I described.

    id admit disch srvc
    111 27sep201 30sep2010 hospital
    111 30sep2010 09dec2010 nursing
    111 10dec2010 18oct2010 hospice

    I know I can put in the subquery a disch_dt+1 or whatever the PM's say they want to look at. I am sure that this will qualify to go into the other pile. As in this whole ID's set of admissions. I am going to try running the query just as the top 1 and see what it gives me. I tried just saying = hospital and doing my readmit program like that but then it was counting other admissions that should fall into other becuase 111 does have hospital admission. And that hospital admission really needs to be in the other because they went somewhere other than hospital after surgery.

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The table structure is set as it is because it comes from our claims database. So I cannot change the way it appears.
    Who said that you could not take the claims data you get and put it into another structure to do your analysis?

    Does your current table have a field that uniquely identifies the record (i.e. a primary key field)?

    If not, you could dump your data into a table that assigns a unique record ID. For example purposes, let's call this table tblAdmissions and the primary key field is called pkAdmissionID. The other fields will remain the same. Now create the following table:



    tblRelatedAdmissions
    -pkRelatedAdmitID primary key, autonumber
    -fkPAdmissionID foreign key to your main admission table
    -fkSAdmissionID foreign key to your main admission table

    You could populate the above table using an append query that in turn would be based on a query very similar to the one I provided (i.e. the one with the subquery)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Block IF End IF Confusion
    By Phred in forum Programming
    Replies: 11
    Last Post: 03-10-2012, 02:18 PM
  2. VB and SQL confusion
    By Wayne311 in forum Programming
    Replies: 29
    Last Post: 01-31-2011, 10:27 PM
  3. Events Confusion?
    By homerj56 in forum Programming
    Replies: 5
    Last Post: 10-27-2010, 01:45 PM
  4. Combo box lookup confusion
    By redpenner in forum Forms
    Replies: 5
    Last Post: 08-19-2010, 08:45 PM
  5. Confusion
    By colenzo in forum Access
    Replies: 4
    Last Post: 07-21-2009, 06:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums