How to count number of inactive employees by month on a rolling 12 month basis (1 Viewer)

Local time
Today, 20:09
Joined
Apr 28, 2022
Messages
39
For the life of me I can't get this to work. I need to identify on a 12 month rolling basis by month how many counsellors didn't have any appointments.

It has to take into account their start and end dates of employment. If the end date is blank they are still employed and if the counsellor is only employed for one day in a month (eg started on the 31st) they are still included in that month's total. The tblCounsellor has ID, start_date and end_date. The tblAppointment has the counsellorID and appt_date. If they don't have any appointments for a particular month then there will be no appointment records. The 12 months start from last month.

I know I can do this in code easy enough just by reading through each counsellor record and tally up which months they were inactive but employed. However I feel there should be a way to do this in a query but I don't know where to start plus my SQL skills are pants. Any ideas?
 

plog

Banishment Pending
Local time
Today, 15:09
Joined
May 11, 2011
Messages
11,611
Please demonstrate your issue with data. 2 sets:

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

B - Expected results of A. Show us what data you expect to end up with when you feed in the data from A.

Again, 2 sets of data--starting and expected results based on the starting.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:09
Joined
May 21, 2018
Messages
8,463
I would make a helper table.
tblMonths
Code:
MonthName
TheYear
MonthDateStart
MonthDateEnd

Although the first two fields are not needed it would simplify my query so do not have to put in a function to get the name and year.
so data would be
January
2022
1/1/2022
1/31/2022

This way I can filter on year.

Using that table I can do a between criteria
qryCounselorAppts
Select CounselorID, MonthName, theYear from tblMonths, tblCounsellor ....
where apptDate >= MonthDateStart and apptDate <= MonthDateEnd and Counselor.StartDate <= MonthDateEnd and Counselor.EndDate >= MonthDateStart.

Save that query and do an aggregate by counselorID and month and count of appts. "qryCountCounsellorAppts"

Now I would make a query qryAvailableCounselorsByMonth

Select CounselorID, MonthName ....
Counselor.StartDate <= MonthDateEnd and Counselor.EndDate >= MonthDateStart.
That would be the list of counselors in a month.

Left join qryAvailableCounselors to qryCountCounsellorAppts by counselorID and Month to see who is working and had no appts.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:09
Joined
May 7, 2009
Messages
19,169
another demo.
i made some "small" queries and easy to follow.
query 6_qryCrosstabByMonth, i think, is what you need.
 

Attachments

  • Counsellors.accdb
    1.5 MB · Views: 165
Local time
Today, 20:09
Joined
Apr 28, 2022
Messages
39
I would make a helper table.
tblMonths
Code:
MonthName
TheYear
MonthDateStart
MonthDateEnd

Although the first two fields are not needed it would simplify my query so do not have to put in a function to get the name and year.
so data would be
January
2022
1/1/2022
1/31/2022

This way I can filter on year.

Using that table I can do a between criteria
qryCounselorAppts
Select CounselorID, MonthName, theYear from tblMonths, tblCounsellor ....
where apptDate >= MonthDateStart and apptDate <= MonthDateEnd and Counselor.StartDate <= MonthDateEnd and Counselor.EndDate >= MonthDateStart.

Save that query and do an aggregate by counselorID and month and count of appts. "qryCountCounsellorAppts"

Now I would make a query qryAvailableCounselorsByMonth

Select CounselorID, MonthName ....
Counselor.StartDate <= MonthDateEnd and Counselor.EndDate >= MonthDateStart.
That would be the list of counselors in a month.

Left join qryAvailableCounselors to qryCountCounsellorAppts by counselorID and Month to see who is working and had no appts.
Thanks this is really useful. The tblMonths makes a lot of sense. I had already built qryCountCounselorAppts in a more convoluted way so you've helped a lot to refine it. I am however now stuck on the very last bit where I couldn't get the join to work. In the world of Venn diagrams what I'm trying to do is A-B where B is a subset of A. So for all counsellors remove the ones that had appointments. I've attached my test db so you can see how I've interpreted your suggestion. It's just the WHERE clause I'm stuck on in qryLazyCounsellors.

This is what should be in the final set. So August '21 John didn't have any appointments.
1654784176229.png
 

Attachments

  • Database2.accdb
    952 KB · Views: 162
Last edited:
Local time
Today, 20:09
Joined
Apr 28, 2022
Messages
39
another demo.
i made some "small" queries and easy to follow.
query 6_qryCrosstabByMonth, i think, is what you need.
Thanks for this. It actually encouraged me to do a test DB which I attached in my reply to MajP. I'm not familiar with cross tabs in Access so it's good to see how you've put it together. I've actually built a dashboard using Access charts and the issue I have with the count is part of a problem in getting the chart right so I have no problem getting a qry to populate the chart but can the same be done with a crosstab? Sorry for my ignorance but I'm relatively a beginner with Access.
 
Local time
Today, 20:09
Joined
Apr 28, 2022
Messages
39
Please demonstrate your issue with data. 2 sets:

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

B - Expected results of A. Show us what data you expect to end up with when you feed in the data from A.

Again, 2 sets of data--starting and expected results based on the starting.
See my response to MajP. Cheers
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:09
Joined
May 21, 2018
Messages
8,463
IMO there is no way to do this in a crosstab. In a crosstab the rows would be the counselors the columns would be the months. How would you be able to show if a counselor is employed that month? If they do not have an appointment it will be Null (or you could make that 0) but no way to tell if they were employed at that time.

Your final solution should look like this. For each month only the actually employed counselors are included. If you filter this on O appts then that list is those employed counselors in that month with no appts.

qryWorkingAppts qryWorkingAppts

CounselorIDcnameyyyymmyearsmonthsmonth_nameNumberOrApptInMonth
5​
Alex202101
2021​
1​
January0
8​
Jimi202101
2021​
1​
January0
5​
Alex202102
2021​
2​
February0
8​
Jimi202102
2021​
2​
February0
5​
Alex202103
2021​
3​
March0
8​
Jimi202103
2021​
3​
March0
1​
John202103
2021​
3​
March0
5​
Alex202104
2021​
4​
April0
8​
Jimi202104
2021​
4​
April0
1​
John202104
2021​
4​
April0
5​
Alex202105
2021​
5​
May1
8​
Jimi202105
2021​
5​
May0
1​
John202105
2021​
5​
May1
5​
Alex202106
2021​
6​
June1
8​
Jimi202106
2021​
6​
June0
1​
John202106
2021​
6​
June1
5​
Alex202107
2021​
7​
July0
8​
Jimi202107
2021​
7​
July0
1​
John202107
2021​
7​
July0
5​
Alex202108
2021​
8​
August1
8​
Jimi202108
2021​
8​
August0
1​
John202108
2021​
8​
August0
5​
Alex202109
2021​
9​
September1
6​
Geddy202109
2021​
9​
September0
8​
Jimi202109
2021​
9​
September0
1​
John202109
2021​
9​
September1
5​
Alex202110
2021​
10​
October1
6​
Geddy202110
2021​
10​
October1
8​
Jimi202110
2021​
10​
October0
2​
Paul202110
2021​
10​
October2
5​
Alex202111
2021​
11​
November2
6​
Geddy202111
2021​
11​
November1
8​
Jimi202111
2021​
11​
November0
2​
Paul202111
2021​
11​
November1
5​
Alex202112
2021​
12​
December2
6​
Geddy202112
2021​
12​
December1
8​
Jimi202112
2021​
12​
December0
2​
Paul202112
2021​
12​
December1
qryCounselorsWorkingInMonth
Shows each counselor that is employed in every month
Code:
SELECT
   C.ID AS CounselorID,
   C.cname,
   M.yyyymm,
   M.years,
   M.months,
   M.month_name,
   C.start_date AS CounselorStartDate,
   C.end_date AS CounselorEndDate
FROM
   tblMonths AS M,
   tblCounsellor AS C
WHERE
   (
((M.start_date) >= [c].[start_date]
      And
      (
         M.start_date
      )
       <= Nz([c].[end_date], Date()))
   )
ORDER BY
   M.years,
   M.months,
   C.cname;

qryApptsInMonth
shows the appointments by counselor.
Code:
SELECT
   tblCounsellor.ID AS CounselorID,
   tblCounsellor.cname,
   Year([appt_date]) AS YearNumber,
   Month([appt_date]) AS MonthNumber,
   Count(tblAppointment.ID) AS ApptsInMonth,
   Format([appt_date], "yyyymm") AS YYYYMM
FROM
   tblCounsellor
   INNER JOIN
      tblAppointment
      ON tblCounsellor.ID = tblAppointment.counsellor_ID
GROUP BY
   tblCounsellor.ID,
   tblCounsellor.cname,
   Year([appt_date]),
   Month([appt_date]),
   Format([appt_date], "yyyymm"),
   tblCounsellor.cname
ORDER BY
   Year([appt_date]),
   Month([appt_date]),
   tblCounsellor.cname;

qryWorkingCounselorAppointments
Shows all the employed counselors per month and the number of appts.
Code:
ELECT
   qryCounselorsWorkingInMonth.CounselorID,
   qryCounselorsWorkingInMonth.cname,
   qryCounselorsWorkingInMonth.yyyymm,
   qryCounselorsWorkingInMonth.years,
   qryCounselorsWorkingInMonth.months,
   qryCounselorsWorkingInMonth.month_name,
   Nz([apptsInMonth], 0) AS NumberOrApptInMonth
FROM
   qryCounselorsWorkingInMonth
   LEFT JOIN
      qryApptsInMonth
      ON (qryCounselorsWorkingInMonth.CounselorID = qryApptsInMonth.CounselorID)
      AND
      (
         qryCounselorsWorkingInMonth.years = qryApptsInMonth.YearNumber
      )
      AND
      (
         qryCounselorsWorkingInMonth.months = qryApptsInMonth.MonthNumber
      )
ORDER BY
   qryCounselorsWorkingInMonth.yyyymm,
   qryCounselorsWorkingInMonth.cname;
 

Attachments

  • MajP_Counselors.accdb
    1.4 MB · Views: 135

Users who are viewing this thread

Top Bottom