Completely Confused about Queries

[dmm1977]

I have created an Access 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.

 

[jdraw]

When you cross post --post the same question in multiple forums - please advise readers that you have done so. Post a link to the other thread.

Here's why.


cross posted http://www.accessforums.net/showthread.php?t=63843

 

[plog]

Let's tackle one report at a time. You choose which one. Then when you do, post sample data to demonstrate your issue and expected results. So, that means 2 sets of data:

A. Starting sample data from your table(s). Include table and field names and enough sample data to cover all cases.

B. Expected results. Show what data you want your query to generate when you feed it the data in A.

A spreadsheet with A and B on their own tabs would be great, or you can post directly into a post and format your data like so:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
Jeff, 12/21/2009, 56
Sally, 1/31/2008, 19
Tim, 5/5/2006, 12

 

[dmm1977]

I have a couple of tables that will come into play. One table has client information on it and the other table has dates they attended therapy with a drop down box for each date entered that indicates whether they met with the program therapist or another therapist. The information on the specific things contained in the 2 tables is below with example data. The primary key in the first (parent) table is PID (client id number assigned by our agency) and all other tables have ID (Autonumber) as the primary key. The foreign key in each (child) other table is PID.

tblClientInfo
PID, LastName, FirstName, DOB, ProgramStartDate, ProgramEndDate, ActiveInProgram (this is a yes/no field)
34291, Adams, Jane, 01/20/2001, 12/23/2016, No Date Entered b/c Client still in program, yes
34789, Smith, Molly, 11/16/2000, 10/16/2016, 01/12/2017, no

tblTherapy
ID, PID, TherapyDate, TherapyProvider (Created using the lookup wizard. I did not link it to another table, but instead entered the following options, Program Therapist, Other Therapist)
1, 34291, 12/27/2016, Program Therapist
2, 34291, 01/09/2017, Other Therapist
3, 34291, 01/12/2017, Program Therapist
4, 34789, 10/20/2016, Program Therapist
5, 34789, 10/27/2016, Program Therapist
6, 34789, 11/06/2016, Other Therapist
7, 34789, 11/19/2016, Program Therapist
8, 34789, 11/24/2016, Program Therapist
9, 34789, 12/01/2016, Program Therapist
10, 34789, 12/18/2016, Other Therapist
11, 34789, 12/23/2016, Program Therapist
12, 34789, 01/06/2017, Program Therapist
13, 34789, 01/11/2017, Other Therapist

I would like to take this data and be able to see how many total therapy contacts each client had in a given month in which they were active in the program and then the average number of therapy sessions per client in the month. For example, in December it would show the following as a report.

December 2016 Therapy Contacts
Client Name Therapy Contacts
Adams, Jane 1
Smith, Molly 3
Average Therapy Hours per Month per Client
In calculating Average Therapy Hours in per Month per Client for December the calculation would look like this.
For Adams, Jane
(Therapy Contacts in December/Days In Program for December) * Total Days in December – This calculation with numbers would be (1/9)*31 = 3.875
For Smith, Molly
This could be calculated the same way that Adams, Jane is calculated, but since Molly was in the program the entire month it doesn’t necessarily have to be calculated. The total is 3.
The Average Client Therapy Hours for December would then be a matter of doing the following (3.875+3)/2.

 

[CJ_London]

cross posted here

http://www.accessforums.net/showthread.php?t=63843

@dmm - please read this link

http://www.excelguru.ca/content.php?184

 

[plog]

Good data post. Just one error: (1/9) * 31 = 3.4444

At least that's what my query produced. I was able to achieve it with 2 queries and a custom function. First the function:

Public Function get_AverageHours(in_Contacts, in_Month, in_Year, in_ProgramStart, in_ProgramEnd) As Double
' gets average hours a patient had in month (in_Month, in_Year) based on hours and time in program (in_ProgramStart, in_ProgramEnd)

ret = -1                                                    ' return value, default is -1 for error

dt_FirstOfMonth = CDate(in_Month & "/1/" & in_Year)         ' makes date of first day of the month searching for
dt_FirstOfNextMonth = DateAdd("m", 1, dt_FirstOfMonth)      ' makes date of first day of next month so can just subtact to get days in month

int_TotalMonthDays = DateDiff("d", dt_FirstOfMonth, dt_FirstOfNextMonth)
  ' total number of days of month submitted for evaluation

If (in_ProgramStart > dt_FirstOfMonth) Then dt_FirstOfMonth = in_ProgramStart
' if started program after first of month, sets program start to that date

If (in_ProgramEnd < dt_FirstOfNextMonth) Then dt_FirstOfNextMonth = in_ProgramEnd
' if ended program before first of next month, sets program end to that date

int_PatientDaysInMonth = DateDiff("d", dt_FirstOfMonth, dt_FirstOfNextMonth)
' gets total days patient was in program in month in question

If int_PatientDaysInMonth > 0 Then ret = (in_Contacts * int_TotalMonthDays) / int_PatientDaysInMonth
' if pateint was in program for the month, it calculates their average hours

get_AverageHours = ret

End Function

Paste that into a module and save. Then, paste this into a new query:

SELECT tblTherapy.PID, Month([TherapyDate]) AS TherapyMonth, Year([TherapyDate]) AS TherapyYear, Count(tblTherapy.PID) AS Contacts
FROM tblTherapy
GROUP BY tblTherapy.PID, Month([TherapyDate]), Year([TherapyDate]);

Name that query 'TherapyContactsByMonth_sub1'. It will actually produce the monthy results you said you wanted. However, to get your Average hours per patient by month, you need this SQL:

SELECT TherapyContactsByMonth_sub1.PID, TherapyContactsByMonth_sub1.TherapyMonth, TherapyContactsByMonth_sub1.TherapyYear, TherapyContactsByMonth_sub1.Contacts, get_AverageHours([Contacts],[TherapyMonth],[TherapyYear],[ProgramStartDate],[ProgramEndDate]) AS AverageHours
FROM TherapyContactsByMonth_sub1 INNER JOIN tblClientInfo ON TherapyContactsByMonth_sub1.PID = tblClientInfo.PID;

Then you can build yet another query upon that query where you can average the averages by month. Let me know if you don't understand anything or have any issues.