query help

[Chris Nebinger]

If there are duplicates in the tblWage, which title do you
use? Is there a date field or something that you can sort
on? This will work for you...


SELECT T2.Training, [LastName]&", "&[FirstName] AS Name,
T3.Title
FROM tblTrainingList AS T2, tblEmployees AS T1 INNER JOIN
(Select Top 1 Title,EmployeeID from tblWage order by [YOUR
DATE FIELD] DESC) AS T3 ON T1.EmployeeID =T3.EmployeeID
WHERE (((T2.Training) Not In (SELECT DISTINCT Training FROM
tblTraining WHERE EmployeeID = T1.EmployeeID)))
AND T3.Title IN ("CN/ON", "Residnetial Supervisor", "Q")
ORDER BY [LastName]&", "&[FirstName];



Chris Nebinger

 

[Chris Nebinger]

Naming the field Date is not recommended, as it is a
reserved word.

SELECT
T2.Training,
[LastName]&", "&[FirstName] AS Name,
T3.Title
FROM
tblTrainingList AS T2,
tblEmployees AS T1 INNER JOIN
(Select Top 1 Title,
EmployeeID
FROM
tblWage
order by [Date] DESC) AS T3
ON T1.EmployeeID =T3.EmployeeID
WHERE
T2.Training Not In (SELECT DISTINCT Training FROM
tblTraining WHERE EmployeeID = T1.EmployeeID)
AND T3.Title IN ("CN/ON", "Residnetial Supervisor", "Q")
ORDER BY [LastName]&", "&[FirstName];

Chris Nebinger

-----Original Message-----
Yes there is a Date field in the tblWage. EmployeeID and Date are the
primary keys. The fields in that table are EmployeeID, Date, Wage, Title,
Status. The title I want it to look at would be the last one, or the newest.
So if they have a entry on 12/1/02, 7/1/03, and 12/1/04, I want it to look
at the title on 12/1/04.

If there are duplicates in the tblWage, which title do you
use? Is there a date field or something that you can sort
on? This will work for you...


SELECT T2.Training, [LastName]&", "&[FirstName] AS Name,
T3.Title
FROM tblTrainingList AS T2, tblEmployees AS T1 INNER JOIN
(Select Top 1 Title,EmployeeID from tblWage order by [YOUR
DATE FIELD] DESC) AS T3 ON T1.EmployeeID =T3.EmployeeID
WHERE (((T2.Training) Not In (SELECT DISTINCT Training FROM
tblTraining WHERE EmployeeID = T1.EmployeeID)))
AND T3.Title IN ("CN/ON", "Residnetial Supervisor", "Q")
ORDER BY [LastName]&", "&[FirstName];



Chris Nebinger

-----Original Message-----
I have a query that shows which employee is missing a training by comparing
it to the training list. The thing I want to do is be able to have it show
only results for certain job codes. I had a response that helped me set up
that SQL but I forgot to mention a point so I need

help
with that. Here is

my SQL:

SELECT T2.Training, [LastName]&", "&[FirstName] AS Name,
T3.Title
FROM tblTrainingList AS T2, tblEmployees AS T1 INNER JOIN
tblWage AS T3 ON T1.EmployeeID =T3.EmployeeID
WHERE (((T2.Training) Not In (SELECT DISTINCT Training FROM
tblTraining WHERE EmployeeID = T1.EmployeeID)))
AND T3.Title IN ("CN/ON", "Residnetial Supervisor", "Q")
ORDER BY [LastName]&", "&[FirstName];


This works except for the fact that the tblWage is a running table with each
employee's wage and title history, so for everytime

they
had a wage increase

and the job title CN/ON is with their name, I'll get duplicate entries in my
results. I need it to look at just the last record

they
have in the tblWage

and go off of what that job title is in that last entry.

Thanks
.

.