425,460 Members | 2,215 Online
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

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

 P: 33 Hi, I have data similar to this: Expand|Select|Wrap|Line Numbers 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 Dec 21 '17 #1
4 Replies

 100+ P: 189 I would do it in two queries.In first query, I'll extract the Month from the given date. Expand|Select|Wrap|Line Numbers 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 : Expand|Select|Wrap|Line Numbers 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? Dec 22 '17 #2

 P: 33 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. Dec 22 '17 #3

 100+ P: 212 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. Expand|Select|Wrap|Line Numbers 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   Dec 28 '17 #4

 P: 33 Just what the doctor ordered! Thanks! Jan 2 '18 #5