Query Parameters

[Mark D.]

I am trying to create an employee turnover query. The table I am pulling from
has the employees name, hire date, and termination date. Of course all the
employees do not have termination dates and that is where I am running into
problems. I want the query to tell me who was terminated in the first quarter
and who is still employed. I don't want the employees we terminated last year
to show up. I set up the first parameter to show hire dates that are equal to
or less than 3/31/09. I set my second parameter to show termination dates
equal to or greater than 1/1/09. The query only shows me employees terminated
and no employees that are still working. I remove the termination date
parameter and shows me employees we let go last year which I do not want. Any
suggestions?

 

[KARL DEWEY]

Try this --
SELECT [employees name], [hire date], [termination date]
FROM YourTable
WHERE ([hire date] <=
DateAdd("q",Format(Date(),"q")-1,DateSerial(Year(Date()),1,1))-1) And
([termination date]

DateAdd("q",Format(Date(),"q")-2,DateSerial(Year(Date()),1,1))-1);

If you also want to see current employees then try this --
SELECT [employees name], [hire date], IIF([termination date] Is Null,
"Employed", ([termination date]) AS Termed_Employed
FROM YourTable
WHERE (([hire date] <=
DateAdd("q",Format(Date(),"q")-1,DateSerial(Year(Date()),1,1))-1) And
([termination date]

DateAdd("q",Format(Date(),"q")-2,DateSerial(Year(Date()),1,1))-1)) Or

([termination date] Is Null);

 

[KenSheridan via AccessMonster.com]

Another approach would be to use a UNION ALL operation e.g.

PARAMETERS [Enter year:] SHORT, [Enter quarter:] SHORT;
SELECT Employee, HireDate, TerminationDate,
"Currently employed" AS EmploymentStatus
FROM Employees
WHERE TerminationDate IS NULL
UNION ALL
SELECT Employee, HireDate, TerminationDate,
"Terminated in quarter " & [Enter quarter:] & ", " & [Enter year:]
FROM Employees
WHERE YEAR(TerminationDate) = [Enter year:]
AND DATEPART("q",TerminationDate) = [Enter quarter:];

As the dates are not being hard-coded in the query this will return the
employees who are currently employed or who's employment was terminated in
the quarter of the year entered as the parameter values when the query is
opened.

Ken Sheridan
Stafford, England