Results 1 to 3 of 3
  1. #1
    dmm1977 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    5

    Overwhelmed with Queries

    Ihave created an Access Form to enter information about different clients I amserving through a grant funded program. The grant requires that we meet certainbenchmarks and I am at the point now where I need to create reports that showour outcomes each month. I know very little about queries or reports in Accessand do not know where to start. I need to be able to do a couple of things. Theclients in the program meet with a therapist and our grant benchmark is that eachclient meets with a therapist at least 3 times a month. My form allows a userto enter in a date and the type of therapy they had. My first question is howwould I write a query where I could generate a report that provides a list ofall of the clients with the number of therapy sessions they had in a givenmonth and also calculates the average number of times the clients as a wholemet with a therapist for the month (total therapy sessions for all clients inJanuary divided by number of clients)? My second question is a little bit morecomplex and only impacts the numbers for a client the first and last month theyare in the program. Sometimes a client may not begin the program until wellinto a month and we do not want our numbers negatively impacted by this. If wedon’t take into account the number of days someone was in the program the firstmonth they enter the program or the last month they are in the program, then wecould end up with numbers that show that we are not meeting our goals. Forexample, if client 1 enters the program on January 24, 2017 and because of thisonly has 1 therapy session in the month, we want to be able to calculate histherapy session number taking into account his entry into the program on01/24/17. When I first created an Excel spreadsheet for this data I actuallyhad a column for each month that indicated the number of days the client was inthe program for the month. Using the above example I then did a calculationthat looked like this (Total Number of Therapy Sessions/Number of Days Clientwas in the program during the month whichwould be 8 in the above example)*Total Number of Days in the Month. So, ifthe 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 Ihave has a program start date and end date and ideally I would be able to useit in doing the calculation, so that calculations done for a client in amonth/year in which they did not begin or end the program does not have to takeinto account the number of days they were in the program that month. Any helpwould be appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    yes Access can do this.
    use a form with start and end date text boxes.
    the queries (and reports) would use this to limit the data.
    similar to:
    select * from table where date between forms!myForm!txtStartDATe and forms!myForm!txtEndDate

    if you keep parent/child related tables then you can analyze anything contained within.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 06-05-2016, 09:23 PM
  2. queries based on union queries
    By vicsaccess in forum Queries
    Replies: 3
    Last Post: 10-10-2015, 07:53 PM
  3. Replies: 9
    Last Post: 08-27-2014, 11:34 AM
  4. Replies: 4
    Last Post: 04-06-2014, 09:08 AM
  5. Replies: 6
    Last Post: 11-13-2013, 04:17 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums