1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Need Help with Access

Discussion in 'Business Applications' started by hotdiamond, Jan 6, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    I have a question that I hope someone can help me with. In my table I am tracking the number of visits a patient has had in a year. To be compliant, they need at least 3 visits per year. So I created an option group and labeled them as follows:

    None
    One visit
    Two visits
    Three visits
    Four or more visits

    Now I have a group for administrative data and another group for chart review (same option group labels). If they did not have the correct number of visits in the administrative data, they could possibly have the correct number of visits from chart review. I thought I could create a query and add the two fields (admin data and chart review) together to get the total number of visits. However, since I have an option group, when I look in my table for the information, none = 1, one visit = 2, and so forth. Therefore, if I add the two fields together, my calculations will not be correct b/c it will be counting the “none” option as well.

    Is there any easy way to fix this so that I can get an accurate count of both the administrative and chart review data together?

    Tonya
     
  2. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    My first impression is that you're tending toward an un-normalized database.

    You can use the BeforeUpdate event for each option group to recalibrate the data before it's recorded in the table. This will require using some VBA code.

    Logically speaking, the count will never be accurate if the patient has had more than four visits of any one type.

    Could you explain a little bit more about the database itself? Particularly, do you generate a record of each patient's visit? If the record was as simple as a patient ID and the date/time of the visit, that'd be enough.

    We can help you with the VBA code if you if that suggestion is what you prefer.

    chris.
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    Chris, Tonya could also just adjust the value by subtracting 1 from each of the fields in the query itself.
     
  4. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    Sorry I am just now getting back to the forum. I've been really busy.

    Thanks for the great suggestions Chris and OBP.

    OBP, your idea sounds like it wouldn't take very much time. So that's what I will try first. What exactly do I need to type in the query to subract 1 from the values?

    Thanks again,
    Tonya
     
  5. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    Disregard the previous message. I figured it out. It works!

    Thanks so much!!!
     
  6. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    (y)

    Glad you sorted it. Again, I'd like to stress that the count of the number of visits will not be accurate if a patient has had four or more of any particular category. This isn't necessarily a problem, but it would be if you wanted to know the number of times a single patient visited your facility.

    chris.
     
  7. hotdiamond

    hotdiamond Thread Starter

    Joined:
    Mar 4, 2004
    Messages:
    161
    Thanks for the concern, Chris. But that will not be a problem. I do not need to know exactly how many visits they've had if it was greater than four. If it's more than four then they would be compliant and that's all the info I need. Thanks again though.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/431700

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice