query giving odd results

[Guest]

I have a query that shows training due by date range for employees with
certain job codes. It works fine for the first job code that I have for
conditions, showing only those that are due between those dates, but for the
job codes listed after that it is showing every training regardless of when
they expire. Any help would be appreciated, I'm sure it's simple, I'm just
overlooking it.

SELECT tblEmployees.LastName, tblEmployees.FirstName, tblTraining.Training,
tblTraining.DateTaken, tblTraining.DateExpires
FROM (tblEmployees INNER JOIN tblTraining ON tblEmployees.EmployeeID =
tblTraining.EmployeeID) INNER JOIN tblWage ON tblEmployees.EmployeeID =
tblWage.EmployeeID
GROUP BY tblEmployees.LastName, tblEmployees.FirstName,
tblTraining.Training, tblTraining.DateTaken, tblTraining.DateExpires
HAVING (((tblTraining.DateExpires) Between [Enter beginning date: example
1/1/04] And [Enter ending date: example 12/31/04]) AND
(((Last(tblWage.Title))="CN/ON")) OR (((Last(tblWage.Title))="Residential
Supervisor")) OR (((Last(tblWage.Title))="Q")) OR
(((Last(tblWage.Title))="Q/Case Coordinator")) OR
(((Last(tblWage.Title))="Team Leader")))
ORDER BY tblEmployees.LastName
WITH OWNERACCESS OPTION;

 

[John Spencer (MVP)]

I doubt if you are going to get consistent results with that query since LAST
essentially returns a random record within the group. However, here is one way
of making the query give you results only for the time frame specified.

SELECT tblEmployees.LastName, tblEmployees.FirstName, tblTraining.Training,
tblTraining.DateTaken, tblTraining.DateExpires
FROM (tblEmployees INNER JOIN tblTraining ON tblEmployees.EmployeeID =
tblTraining.EmployeeID) INNER JOIN tblWage ON tblEmployees.EmployeeID =
tblWage.EmployeeID
GROUP BY tblEmployees.LastName, tblEmployees.FirstName,
tblTraining.Training, tblTraining.DateTaken, tblTraining.DateExpires
HAVING tblTraining.DateExpires Between
[Enter beginning date: example 1/1/04] And
[Enter ending date: example 12/31/04] AND
Last(tblWage.Title) IN
("CN/ON","Residential Supervisor","Q","Q/Case Coordinator","Team Leader")
ORDER BY tblEmployees.LastName
WITH OWNERACCESS OPTION;

What you probably want to use is the latest in time tblWage.Title for each
individual. That would probably be determined using the MAXIMUM Date that they
were assigned that wage title. WE cannot see how that would be determined from
what you have posted.

 

[Guest]

My wage table has these fields:

DateofChange
DayWage
NightWage
Title
Status

So I guess what you were asking was if there was a date associated with the
title and the answer would be yes. Their last title would be that of the
last DateofChange.

I doubt if you are going to get consistent results with that query since LAST
essentially returns a random record within the group. However, here is one way
of making the query give you results only for the time frame specified.

SELECT tblEmployees.LastName, tblEmployees.FirstName, tblTraining.Training,
tblTraining.DateTaken, tblTraining.DateExpires
FROM (tblEmployees INNER JOIN tblTraining ON tblEmployees.EmployeeID =
tblTraining.EmployeeID) INNER JOIN tblWage ON tblEmployees.EmployeeID =
tblWage.EmployeeID
GROUP BY tblEmployees.LastName, tblEmployees.FirstName,
tblTraining.Training, tblTraining.DateTaken, tblTraining.DateExpires
HAVING tblTraining.DateExpires Between
[Enter beginning date: example 1/1/04] And
[Enter ending date: example 12/31/04] AND
Last(tblWage.Title) IN
("CN/ON","Residential Supervisor","Q","Q/Case Coordinator","Team Leader")
ORDER BY tblEmployees.LastName
WITH OWNERACCESS OPTION;

What you probably want to use is the latest in time tblWage.Title for each
individual. That would probably be determined using the MAXIMUM Date that they
were assigned that wage title. WE cannot see how that would be determined from
what you have posted.

I have a query that shows training due by date range for employees with
certain job codes. It works fine for the first job code that I have for
conditions, showing only those that are due between those dates, but for the
job codes listed after that it is showing every training regardless of when
they expire. Any help would be appreciated, I'm sure it's simple, I'm just
overlooking it.

SELECT tblEmployees.LastName, tblEmployees.FirstName, tblTraining.Training,
tblTraining.DateTaken, tblTraining.DateExpires
FROM (tblEmployees INNER JOIN tblTraining ON tblEmployees.EmployeeID =
tblTraining.EmployeeID) INNER JOIN tblWage ON tblEmployees.EmployeeID =
tblWage.EmployeeID
GROUP BY tblEmployees.LastName, tblEmployees.FirstName,
tblTraining.Training, tblTraining.DateTaken, tblTraining.DateExpires
HAVING (((tblTraining.DateExpires) Between [Enter beginning date: example
1/1/04] And [Enter ending date: example 12/31/04]) AND
(((Last(tblWage.Title))="CN/ON")) OR (((Last(tblWage.Title))="Residential
Supervisor")) OR (((Last(tblWage.Title))="Q")) OR
(((Last(tblWage.Title))="Q/Case Coordinator")) OR
(((Last(tblWage.Title))="Team Leader")))
ORDER BY tblEmployees.LastName
WITH OWNERACCESS OPTION;