Need query to show status on first day of month but data has dates throughout month.

[Shawn29316]

Hi,

I have data similar to this:

RecordNum     Date     EmployeeNum   Status
126         9/18/2017     10211        3
127         9/20/2017     10212        2
128         9/20/2017     10213        2
129         9/21/2017     10214        3
130         9/21/2017     10215        2
131         9/26/2017     10211        2
132         9/26/2017     10212        3
133         9/28/2017     10213        1
134         9/28/2017     10214        1
135         9/29/2017     10215        3
136         10/3/2017     10212        1
137         10/3/2017     10214        1
138         10/4/2017     10215        3
139         10/5/2017     10218        1
140         10/5/2017     10224        1

I need to write a query to show the average number of employees at status 3 on the first day of each month, over a rolling 12 months. The data is updated on an irregular schedule so there's no date corresponding to 10/1, in this case. The last records entered each month represent the status at month end but I don't know how to write queries to extract that over the course of a year. I'd appreciate any help you could provide!

Thanks,
Shawn

[Narender Sagar]

I would do it in two queries.In first query, I'll extract the Month from the given date.

SELECT Test.RecordNum, Test.RDate, Test.EmpNum, Test.Status, Month([RDate]) AS RMonth
FROM Test;

(where Test is the name of your table. Similar way, You may also need to extract Year from the date, if required)
Then choose above query and make another query :

SELECT TestQry2.RMonth, Count(TestQry2.EmpNum) AS CountOfEmpNum, TestQry2.Status
FROM TestQry2
GROUP BY TestQry2.RMonth, TestQry2.Status
HAVING (((TestQry2.Status)=3));

(Where TestQry2 is the name of your first query)
You will get answer as Sep : 4; Oct : 1.
I'm just counting the number of Employees having status 3 (I think this is what you are looking for). But, If you need Average number of Employees- I'd like you to confirm again?

[Shawn29316]

Narender,
Thanks for your quick help with this. Your solution got me a lot further than I could have gotten on my own but it's not quite what I need. I need to know how many employees were status 3 on the last recorded date every month....and that date could vary by employee.

[gnawoncents]

If I understand your requirement correctly, you want to know the total number of employees with records for each month and of them, how many were status 3 on the last date they worked that month. If so, the code below will get you there.

Notes:
- This code pulls the data from a table named tbRecords and runs using a button named btnAVG. The field names in the table are fldEmployeeNum, fldRecordNum, fldDate, and fldStatus.
- You'll have to modify it for your specific field names and for how you want the data output to be handled (this code simply has it display in a message box as a list of all the months).
- You will also have to tweak the code if dealing with datasets that span more than one year, but that would be easy enough.

Code follows.

Private Sub btnAVG_Click()

'Establish database and recordset
Dim dbsAVG As Database
Dim rstAVG As Recordset
Set dbsAVG = CurrentDb

'Prep Integers and assign defaults
Dim intMonth As Integer
Dim intStatus As Integer
Dim intEmployee As Integer
Dim int3Count As Integer
Dim intECount As Integer

intMonth = 1
intStatus = 0
int3Count = 0
intECount = 0
intEmployee = 0

Dim strTotals As String

'Create a recordset for one month at a time and sort by employee number then date.
'The last record for each employee will be the last date they worked that month.
'---------------------------------------------------------------------------------
Do Until intMonth = 13
    Set rstAVG = dbsAVG.OpenRecordset("SELECT * FROM tbRecords WHERE Month(fldDate) = " & intMonth & " ORDER BY fldEmployeeNum, fldDate")
        
        'Reset Integers except Month count
        intStatus = 0
        int3Count = 0
        intECount = 0
        intEmployee = 0
        
        If rstAVG.BOF = False And rstAVG.EOF = False Then 'Recordset is not empty
            rstAVG.MoveLast
            rstAVG.MoveFirst
            
            'Capture the Employee info to test for a change in Employee
            intEmployee = rstAVG!fldEmployeeNum
            
            Do While Not rstAVG.EOF
                If rstAVG!fldEmployeeNum <> intEmployee Then
                'this is a different (not first) employee--count info from the previous employee's last record (last day they worked that month)
                    intECount = intECount + 1 'Add 1 to the employee count for the month
                    If intStatus = 3 Then
                        'If the Employee status on the last day they worked that month is a 3, then add 1 to the Status count
                        int3Count = int3Count + 1
                    End If
                End If
                                
                'Capture current record info for later use
                intEmployee = rstAVG!fldEmployeeNum
                intStatus = rstAVG!fldStatus
                
              rstAVG.MoveNext
            Loop
            
            'Capture the data from the last record (last day worked of given month by last employee in list)
            intECount = intECount + 1 'Add 1 to the employee count for the month
            If intStatus = 3 Then
                'If the Employee status on the last day they worked that month is a 3, then add 1 to the Status count
                int3Count = int3Count + 1
            End If
        End If
        
        'Capture results in Totals string as  Month: then number of employees with 3 at end of month / total number of employees with records that month (e.g. 1: 3/5)
        strTotals = strTotals & intMonth & ": " & int3Count & "/" & intECount & vbCrLf
    
    intMonth = intMonth + 1 'Advance to the next month value
Loop

MsgBox strTotals 'Display all months and data

End Sub

[Shawn29316]

Just what the doctor ordered! Thanks!