By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
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
  1. RecordNum     Date     EmployeeNum   Status
  2. 126         9/18/2017     10211        3
  3. 127         9/20/2017     10212        2
  4. 128         9/20/2017     10213        2
  5. 129         9/21/2017     10214        3
  6. 130         9/21/2017     10215        2
  7. 131         9/26/2017     10211        2
  8. 132         9/26/2017     10212        3
  9. 133         9/28/2017     10213        1
  10. 134         9/28/2017     10214        1
  11. 135         9/29/2017     10215        3
  12. 136         10/3/2017     10212        1
  13. 137         10/3/2017     10214        1
  14. 138         10/4/2017     10215        3
  15. 139         10/5/2017     10218        1
  16. 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
Share this Question
Share on Google+
4 Replies


Narender Sagar
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
  1. SELECT Test.RecordNum, Test.RDate, Test.EmpNum, Test.Status, Month([RDate]) AS RMonth
  2. 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
  1. SELECT TestQry2.RMonth, Count(TestQry2.EmpNum) AS CountOfEmpNum, TestQry2.Status
  2. FROM TestQry2
  3. GROUP BY TestQry2.RMonth, TestQry2.Status
  4. HAVING (((TestQry2.Status)=3));
  5.  
(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

gnawoncents
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
  1. Private Sub btnAVG_Click()
  2.  
  3. 'Establish database and recordset
  4. Dim dbsAVG As Database
  5. Dim rstAVG As Recordset
  6. Set dbsAVG = CurrentDb
  7.  
  8. 'Prep Integers and assign defaults
  9. Dim intMonth As Integer
  10. Dim intStatus As Integer
  11. Dim intEmployee As Integer
  12. Dim int3Count As Integer
  13. Dim intECount As Integer
  14.  
  15. intMonth = 1
  16. intStatus = 0
  17. int3Count = 0
  18. intECount = 0
  19. intEmployee = 0
  20.  
  21. Dim strTotals As String
  22.  
  23. 'Create a recordset for one month at a time and sort by employee number then date.
  24. 'The last record for each employee will be the last date they worked that month.
  25. '---------------------------------------------------------------------------------
  26. Do Until intMonth = 13
  27.     Set rstAVG = dbsAVG.OpenRecordset("SELECT * FROM tbRecords WHERE Month(fldDate) = " & intMonth & " ORDER BY fldEmployeeNum, fldDate")
  28.  
  29.         'Reset Integers except Month count
  30.         intStatus = 0
  31.         int3Count = 0
  32.         intECount = 0
  33.         intEmployee = 0
  34.  
  35.         If rstAVG.BOF = False And rstAVG.EOF = False Then 'Recordset is not empty
  36.             rstAVG.MoveLast
  37.             rstAVG.MoveFirst
  38.  
  39.             'Capture the Employee info to test for a change in Employee
  40.             intEmployee = rstAVG!fldEmployeeNum
  41.  
  42.             Do While Not rstAVG.EOF
  43.                 If rstAVG!fldEmployeeNum <> intEmployee Then
  44.                 'this is a different (not first) employee--count info from the previous employee's last record (last day they worked that month)
  45.                     intECount = intECount + 1 'Add 1 to the employee count for the month
  46.                     If intStatus = 3 Then
  47.                         'If the Employee status on the last day they worked that month is a 3, then add 1 to the Status count
  48.                         int3Count = int3Count + 1
  49.                     End If
  50.                 End If
  51.  
  52.                 'Capture current record info for later use
  53.                 intEmployee = rstAVG!fldEmployeeNum
  54.                 intStatus = rstAVG!fldStatus
  55.  
  56.               rstAVG.MoveNext
  57.             Loop
  58.  
  59.             'Capture the data from the last record (last day worked of given month by last employee in list)
  60.             intECount = intECount + 1 'Add 1 to the employee count for the month
  61.             If intStatus = 3 Then
  62.                 'If the Employee status on the last day they worked that month is a 3, then add 1 to the Status count
  63.                 int3Count = int3Count + 1
  64.             End If
  65.         End If
  66.  
  67.         '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)
  68.         strTotals = strTotals & intMonth & ": " & int3Count & "/" & intECount & vbCrLf
  69.  
  70.     intMonth = intMonth + 1 'Advance to the next month value
  71. Loop
  72.  
  73. MsgBox strTotals 'Display all months and data
  74.  
  75. End Sub
  76.  
Dec 28 '17 #4

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

Post your reply

Sign in to post your reply or Sign up for a free account.