?Subtract 2 fields: Start-End

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

For employee movement tracking, I need get Year end employee number by
division. Table example is as follow:-
Div StaffStart StaffLeave
A 2005Q1
B Prior2005 2005Q2
D 2005Q2 2005Q4

The rpt sample should provide YE employee balance that Div A=1, and Div
B&D=0 in pivot table

would appreciate advice on how to make a subtracting query

Also, would apprc comment if the table design is poor.
 
Calculating a date can be done with the DateDiff() function. See the help
file for the usage.

I don't really understand anything about your table below, or the
explanation of what it should provide.

Feel free to list your current actual table structures.
 
Many thanks for your help, Mr Clark.

My actual table has the following fields:
(1)EmployeeID
(2)EmployeeName
(3)Division (multidivision)
(4)StaffStartDate
(5)StaffTeminateDate

I need to use the data to produce a report/table that gives number of
employees in each division at year end.

My thought is to do query counting number of "staffstartdate" minus number
of "Stafftermninatedate".

Would appreciate advice on how to do it
 
This will give you the count in each division as long as the division has at
least one employee that has not been terminated.
SELECT Count(EmployeeID) as NumberEmployees, Division
FROM TABLEName
WHERE StaffTerminateDate is Null
GROUP BY Division

The following may give you what you need if you need the zeros.

Two query solution: Use the query above (call it QStillHere) and join it to the
table in a second query

SELECT Count(A.EmployeeID) as NumberEmployees, A.Division
FROM TABLEName as A LEFT JOIN QStillHere As B
On A.Division = B.Division
GROUP BY A.Division

UNTESTED One query solution
SELECT Count(A.EmployeeID) as NumberEmployees, A.Division
FROM TABLEName as A LEFT JOIN
(SELECT Count(EmployeeID) as NumberEmployees, Division
FROM TABLEName
WHERE StaffTerminateDate is Null
GROUP BY Division) As B
 
Back
Top