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.

Access Query Help

Discussion in 'Business Applications' started by dmm1977, Jan 13, 2017.

Thread Status:
Not open for further replies.
  1. dmm1977

    dmm1977 Thread Starter

    Joined:
    Jan 13, 2017
    Messages:
    1
    I have created an Access 2010 Form to enter information about different clients I am serving through a grant funded program. The grant requires that we meet certain benchmarks and I am at the point now where I need to create reports that show our outcomes each month. I know very little about queries or reports in Access and do not know where to start. I need to be able to do a couple of things. The clients in the program meet with a therapist and our grant benchmark is that each client meets with a therapist at least 3 times a month. My form allows a user to enter in a date and the type of therapy they had. My first question is how would I write a query where I could generate a report that provides a list of all of the clients with the number of therapy sessions they had in a given month and also calculates the average number of times the clients as a whole met with a therapist for the month (total therapy sessions for all clients in January divided by number of clients)? My second question is a little bit more complex and only impacts the numbers for a client the first and last month they are in the program. Sometimes a client may not begin the program until well into a month and we do not want our numbers negatively impacted by this. If we don’t take into account the number of days someone was in the program the first month they enter the program or the last month they are in the program, then we could end up with numbers that show that we are not meeting our goals. For example, if client 1 enters the program on January 24, 2017 and because of this only has 1 therapy session in the month, we want to be able to calculate his therapy session number taking into account his entry into the program on 01/24/17. When I first created an Excel spreadsheet for this data I actually had a column for each month that indicated the number of days the client was in the program for the month. Using the above example I then did a calculation that looked like this (Total Number of Therapy Sessions/Number of Days Client was in the program during the month which would be 8 in the above example)*Total Number of Days in the Month. So, if the client in the example given above attended 1 therapy session in the month, the calculation would be (1/8)*31, which would equal 3.875. One of the forms I have has a program start date and end date and ideally I would be able to use it in doing the calculation, so that calculations done for a client in a month/year in which they did not begin or end the program does not have to take into account the number of days they were in the program that month. Any help would be appreciated.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,766
    I can help you with this.
    You may not be able to do this with a single query, but it should be fairly straight forward.
    Queries can do the same calculation as your Excel one.
    They can do totalling and averaging etc as well.

    Some dummy data would be very helpful, preferably in Excel as I have Access 2007 and it might not be able to read an Access 2010 database if it uses advanced featuers.
     
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/1183821

  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