Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Can't get query to filter2006 (2002)

    Hi,

    I

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Can't get query to filter2006 (2002)

    Look at the attachment.

    I use one query to find the distinct episodes (ie. admittance dates for each patient), another to find the discharges, then an unmatched query to find the episodes that don't have a discharge.
    Regards
    John



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

    Re: Can't get query to filter2006 (2002)

    John has already solved your problem, but I'm wondering whether patients really have to be identified by their first and last names. That would seem to be prone to error - the same patient could be entered in different ways, and different patients could have the same name. You should use some kind of unique patient identifier, such as SSN.

    Note: John used admit_dt instead of soc_dt. In your sample database, this doesn't make a difference for the result, since admit_dt = soc_dt for all records.

    The attached database demonstrates a way to select active patients in one query, using a subquery in the criteria for soc_dt.

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can't get query to filter2006 (2002)

    Thanks! I had tried using more than one query and then combining them to do the filter but for the life of me couldn't get it to work right. I couldn't "see" it in my head. Seeing it in your example made it clearer as to where I was going wrong!

    Leesha

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can't get query to filter2006 (2002)

    Thanks Hans! Your point on the SSN is well taken and I will certainly use that as the unique identifier. John's approach is easier for my head to understand but the stubborn side of me wants to understand the code you provided, and better yet to be able to one day write it out on my own (I'm getting there!). I don't understand the purpose of all the "t" in the code. IE:

    Not In (SELECT t.soc_dt FROM tblMedicareEpisodeReport AS t WHERE t.last_nm=[tblMedicareEpisodeReport].[last_nm] AND t.first_nm=[tblMedicareEpisodeReport].[first_nm] AND t.dis_dt<>" - -")

    What does it stand for since it isn't actually part of the item name.........Ie [soc_dt] vs t.soc_dt.

    Thanks,
    Alicia

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

    Re: Can't get query to filter2006 (2002)

    The t is used as an (arbitrary) alias for the table name in the subquery. I want the subquery to select only records for the "current" patient in the main query. Because the main query and the subquery are based on the same table, I need some way to distinguish between the two. So the subquery uses

    SELECT ... FROM tblMedicareEpisodeReport AS t

    This specifies t as a temporary alias for the real table name. In the WHERE part, t.fieldname refers to a field from the subquery, and tblMedicareEpisodeReport.fieldname to a field from the main query.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Can't get query to filter2006 (2002)

    As I sit here reading this I'm howling laughing thinking how unrealistic is that I will ever "get it", but then I again, I would never have dreamed I do the things I'm doing now due to all of your help!

    Thanks for the explanation!

    Leesha

Posting Permissions

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