Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query question (2003)

    I work in a hospital and have been asked to pull some data out of an Access database that I did not create. It is for tracking patients enrolled in a diabetic program. The patient may follow up at any of our 4 facilities.
    I have created queries that give them what they want - except some patients visit more than one facility during their program. On queries that need to know the facility - some patients are listed twice because they used more than one facility. That is okay, the people who get the report understand that - but they would like to know who those patients are. Is there a way to write a query to show me only the patients that attended more than one facility?

    I've looked around websites to see if I can find something similar - but I cannot. It sounds simple enough, but I must not be thinking about it in the right way.

    I've attached a stripped down version with some test data in it. It has examples from 01/01/08 - 03/15/08.

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

    Re: Query question (2003)

    I don't see an attachment...

    You can create a totals query that groups by the patient ID and counts the facilities. Then specify >1 as condition in the Criteria line for the count field. The query will return the patient IDs with more than one visit.

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2003)

    That's perfect, thank you so much!
    It was so simple - I didn't even think that way.
    Vicky

  4. #4
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2003)

    Sorry, I responded too quick.. I need the query to show only the patients that attended a different facility. Most of the time, the patient folows up at the same hospital for the visits. I need to know the ones that went to a different facility for a visit.

    Example. Mary Smith had 4 visits - all at Mount Carmel West Hospital (that's typical). But Susie Jones had 3 visits at Mount Carmel West Hospital and 1 visit at Mount Carmel East Hospital. How could I identify which patients visited 2 or more different facilities?
    I tried the attachment again.
    Thnaks so much,
    Vicky

    The query that you suggested gave me patients that had more than one visit but most of them were visiting the same hospital each time.
    Attached Files Attached Files

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

    Re: Query question (2003)

    You can do this in two steps:

    1) A totals queries that lists the unique patient - site combinations.
    2) Another totals query based on the first one that returns the patients with more than one site.

    See the attached version.
    Note: I changed the text field Site in the tbl Encounters table to a number field SiteID that links to the primary key in tbl Sites.
    Attached Files Attached Files

  6. #6
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2003)

    Thank you so much, that does make sense to me and works great.

    I did look at changing the text field Site in the tbl Encounters table to a number field SiteID in the past. I work in a copy - good thing because I lost that data. - I have not played with that since, I'm afraid to :-) Is it easy enough? They have several tables not linked -like that one.

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

    Re: Query question (2003)

    You can change it as follows:

    - Create a query based on tbl Encounters and tbl Site. They will be joined on the Site field automatically.
    - Add the Site field from tbl Encounter to the query grid.
    - Select Query | Update Query.
    - Enter [SiteID] in the Update to line.
    - Select Query | Run and confirm.
    - Discard the query.
    - Delete the relationship between tbl Site and tbl Encounters in the Relationships window.
    - Open tbl Encounters in design view.
    - Change the name of the Site field to SiteID.
    - Change its Date Type to Number.
    - Clear the Default Value property.
    - Set the Dependent Column property (in the Lookup tab) to 1.
    - Save & close the table.
    - Create a new relationship between tbl Site and tbl Encounters on SiteID.
    - Enforce referential integrity.

    This takes a lot longer to write down than it takes to actually do it!

  8. #8
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query question (2003)

    I will do this. Thank you!
    I've learned 3 valuable things today - that I did not use in the past. I always made these things harder.
    Take care,
    Vicky

Posting Permissions

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