Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Determine PREVIOUS offense (A2K)

    Hello All,

    I will do my best to NOT be lengthy in my description, as most of time I am.

    Here's my situation:

    I have a main recordset--tIPDEMO

    This table holds ALL admissions and discharges data for all members.

    I have a query, using tIPDEMO, that HANS helped me with some many months ago <post#=597,838>post 597,838</post#> , to determine the members who discharged and then readmitted w/in a 30 day time frame. All works very well.

    Now the powers that be want to include the PREVIOUS discharge data that created the READMIT as part of the report.

    The problem is, is that the 30DayReadmit query ONLY extracts the data that CREATED the offense. How can I now include the DISCHARGED offense in the query?

    Hopefully that makes sense and someone can provide some insight.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine PREVIOUS offense (A2K)

    The query from the post you refer to contains two copies of the table - you should be able to retrieve information from both copies.

    If that doesn't help, please provide sufficient information for us to help you.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine PREVIOUS offense (A2K)

    I'm so struggling here...and I just absolutly don't know what to do next.

    I made an error in my original post, I was sure it was Hans' solution I was using and it isn't. I therefore, sent everyone to a post that doesn't apply here. To everyone, I apologize.

    To set things straight. I'm using a subquery to retrieve the 30 day readmits:

    <code>DaysSinceLastVisit: [sfd]-nz((SELECT TOP 1 ID.STD FROM tIP_DEMOS_DX AS ID WHERE ID.STD< tIP_DEMOS_DX.SFD AND ID.SSN=tIP_DEMOS_DX.SSN ORDER BY ID.STD DESC,[sfd]+1)</code>

    What this does is counts the days between discharge and admit. Then I have "between 0 and 30 in the criteria to only pull those records.

    However, because of this, I don't have anything with the PRIOR DISCHARGE information ... which of course is now what I need.

    Hans, I've looked at the original solution you provided for days, trying to come up with a way of retrieving JUST the DISCHARGE that creats the NEXT record -- the 30 day readmit OFFENSE and I can't find a way of getting it.

    I've attached examples of what I need but can't get. Hopefully that will make more sense.
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine PREVIOUS offense (A2K)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine PREVIOUS offense (A2K)

    Hope you or someone can provide some insight...because I'm totally lost on this one....I mean more lost than I normally am.
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine PREVIOUS offense (A2K)

    See if the query qryPrevSFD in the attached version does what you want. It is based on qryPrevSTD.

    Note: I removed the code table since none of the discharge codes in tttt occurred in the code table.
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine PREVIOUS offense (A2K)

    Yes Hans, I believe it will do what I'm looking for. Now the only thing I need to resolve is the criteria issue. I use a DateSerial function for SFD which, each month pulls data from the previous month. It works fine. I'm only concerned that the criteria will omit the previous records. But I can find a resolution to that.

    Thanks for your assistance...it's greatly appreciated.....sincerely it is.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine PREVIOUS offense (A2K)

    OK Hans....I'm so very close...I can barely breath....I think the syntax you used in the subquery is giving me a new problem.

    <code>PrevSTD: CDate(DMax("STD","tttt","STD<#" & Format([SFD],"mm/dd/yyyy") & "# AND SSN=" & Chr(34) & [SSN] & Chr(34)))</code>


    In the case(s) WHERE there ISN'T a PREVIOUS discharge (STD) "invalid use of NULL" is returned. If I remove the criteria <code>>=[SFD]-30</code>
    then, #ERROR# is returned in the records where there isn't a previous std and a valid Date where there is a valid std.

    Any suggestions?
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine PREVIOUS offense (A2K)

    Please attach a sample database where the problem occurs.

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine PREVIOUS offense (A2K)

    Here it is...
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Determine PREVIOUS offense (A2K)

    You can use the Nz function (more or less like your original expression) to avoid problems with Null values.. See attached version, I changed the definition of PrevSTD.
    Attached Files Attached Files

  12. #12
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determine PREVIOUS offense (A2K)

    I think I'm gonna cry,,,,,ok...I am crying.....thank you so much....
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •