Find Min and Max in a selected date range

[rkalapura]

I have a HR database with a "Employee" table, where we keep all our employee records. The main fileds are EmplId, Lname, Fname, JoinDate, LastDay, JobStat and much more. JoinDate is the date when the employ join the company and LastDay is the date when employ resigns or fired or permanantly leaving company for any reason. Every year company has to submit some reports to the local Govt offices on the employee status data of full year, starting from 01 September to next year 01 Septemper. I set everything exactly as they need in a button click. But I am having difficulty to set one query where they need dates when we had the min and max number of employees during that year. The logic behind this is, we should have the count of employees on first of September of the selecting year, and whenever a new employee join the company, the count should increase by one and whenever an employee leaves the company, count should decrease by one. Then I need to find the dates when we had the min / max number of employees during that year. FYI, now 2000+ employee records are in the database. New people joining and also leaving day by day. I mean, from last Sept till date, 43 people join the company, out of which 24 of them already resigned. Appreciate your help to set up the query/Queries to solve this. Is this make sense or need to have more info on database?



Thank you in advance. God Bless You.

[burrina]

DCount("*","[YourTableName]","[DateField] = " & "Date() DateCriteria")
= Nz(DMax("YourFieldName", "CustomerID")

Hope this gets you started in the right direction.

Good Luck With Your Project.

[orange]

Here's a sample database that should be helpful. I mocked up a table with slightly different field names than yours, but similar enough for test. I used a union query and a procedure with a temporary table. The database includes the mockup table and a module containing the procedure. The union query is set up in sql within the proc. The procedure is commented.

Hope it's useful.

[rkalapura]

Thank you Burrina for assistance. But I am lost here. Can you please explain little bit more on this?

My table name is "Employees". I have two date fields [joindate] and and [lastday]. What is the Date criteria I need to use? Dmax of which Field name and what is the customerid? All this make me confuse. Appreciate your help.

Thank you and God Bless

[rkalapura]

Thank you Orange for your help. I have seen your sample database and it is working fine. I copied your VB code and edit it with my tables and date sources, it gives many errors. Frankly speaking, I am not very good in VB, but can understand. Is there any equvalant access 2010 macro functions available?
Thank you

[orange]

I don't use macros. It was built with AC2010 and saved as .mdb.

If you are having questions, please ask. The vba isn't complex, so maybe just a few concepts.

What errors are you getting?

[rkalapura]

Thank you Orange for your assistance. I have enclosed the modified code which I am using and getting the error. I may be missing something. Please have a look and advise. The error message snapshot also enclosed: FYI, my datas are coming from a form where the user select a year and the values store automatically in these unbound fields. Employee strength of the starting year is from text field [Text169] and its source is a querry. Dateto and Datefrom are the fields we get the starting year and ending year dates, depends the user select the year from selectyear combo.

Sub GetMaxMinEmployees()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset 'for temp table
Dim sCtr As Integer 'starting count at beginning of fiscal year let's say 15 employees
Dim MaxMsg As String
Dim MinMsg As String
Dim strSQL_DROP As String
Dim strSQL_Create As String
10 Set db = CurrentDb
'Delete existing copy of this table
20 On Error Resume Next
30 strSQL_DROP = "DROP TABLE tblEmpMaxMin;"
40 DoCmd.RunSQL strSQL_DROP
50 On Error GoTo GetMaxMinEmployees_Error
60 strSQL_Create = "CREATE TABLE tblEmpMaxMin" & _
" (ReviewDate DateTime, EmpCount int);"
70 DoCmd.RunSQL strSQL_Create
80 Set rs1 = db.OpenRecordset("tblEmpMaxMin")

90 sCtr = [Forms]![summary vets-100]![Text169] ' employees on strength at start of fy picks from a text field of form ************
' **** The union query ***********
Dim UnionQuery As String
100 UnionQuery = "select fname,hiredate as Ddate, 1 as Cnt " _
& "FROM employees where termdate is null and hireDate>=#[forms]![summary vets-100]![datefrom]# " _
& "UNION SELECT employees.fname, employees.termdate AS ddate, -1 AS Cnt " _
& "FROM employees " _
& "WHERE (employees.termdate)<#[forms]![summary vets-100]![dateto]# and employees.termdate >=#[forms]![summary vets-100]![datefrom]# " _
& "ORDER BY ddate;"
110 Set rs = db.OpenRecordset(UnionQuery) '"Employees_Union")
120 Do While Not rs.EOF
'do stuff
130 sCtr = sCtr + rs!Cnt
'Debug.Print "Emp count on " & rs!dDate & " is " & sCtr 'for debugging
140 rs1.AddNew
150 rs1!ReviewDate = rs!dDate
160 rs1!EmpCount = sCtr
170 rs1.Update
180 rs.MoveNext
190 Loop
200 rs1.Close 'rs1
210 MaxMsg = "Max Employees " & DMax("EmpCount", "tblEmpMaxMin") & " on " _
& DLookup("ReviewDate", "tblEmpMaxMin", "EmpCount =" & DMax("EmpCount", "tblEmpMaxMin"))
220 MinMsg = "Min Employees " & DMin("EmpCount", "tblEmpMaxMin") & " on " _
& DLookup("ReviewDate", "tblEmpMaxMin", "EmpCount =" & DMin("EmpCount", "tblEmpMaxMin"))
230 Debug.Print MaxMsg & vbCrLf & MinMsg
240 MsgBox MaxMsg & vbCrLf & MinMsg, vbOKOnly
250 On Error GoTo 0
260 Exit Sub
GetMaxMinEmployees_Error:
270 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetMaxMinEmployees of Module AWF_Related " & vbCrLf & " in Line " & Erl
End Sub

It is not allowing me to attach a error snap shot. The error popup reads like this: "Error 3000 (Reserved error(-3201); there is no message for this error.) in the procedure GetMaxMinEmployees of Module AWF_Related in Line 110".

Appreciate your help.

Thank you and God Bless!

[orange]

Just saw this response.

I used a number for sCtr.

I built a union query in code and called the variable UnionQuery.

The code was intended to give you a working example that you could modify as needed.

Your form must be open for your code to get a value in sCtr.

[rkalapura]

It is connected to the afterupdate event of the combo in the form. The error line is 110. I dont know why?

[orange]

Please post a copy of your database -- remove anything private/confidential.