Simple Query...HELP !

[Deam]

I am trying to find out what the most recent Location and job title was of all the Terminated employees.Information is in 3 tables, the common field is EmployeeID:
When an employee is terminated then either there is a new Current Status Record with the Status Code of 'Terminated' or there is an end date in all the tables.
Table_Job[for job title] Fields:EmployeeID,JobStartDate,JobEndDate,JobCurrentFlag,JobTitle
Table_Location[for Location]Fields:EmployeeID,LocationStartDate,LocationEndDate,LocationCurrentFlag,LocationCode
Table_Status[for Status Code...in this case Terminated]Fields:EmployeeID,StatusStartDate,StatusEndDate, StatusCurrentFlag, StatusCode
Table_Employee[for EmployeeName]Fields:EmployeeID,EmployeeName

-------------------------------
SELECT DISTINCT
E.EmployeeName, Tj.JobTitle, Tl.LocationCode,Ts.StatusCode
FROM Table_Employee E INNER JOIN
Table_Status Ts ON
E.EmployeeID = Ts.EmployeeID INNER JOIN
Table_Job Tj ON
Ts.EmployeeID = Tj.EmployeeID INNER JOIN
Table_Location Tl ON
Ts.EmployeeID = Tl.EmployeeID AND
Tj.EmployeeID = Tl.PEmployeeID
WHERE (Ts.StatusCode = 'Terminated') AND
(Ts.StatusCurrentFlag = 1) AND
(Tj.JobCurrentFlag = 1) AND
(Tl.LocationCurrentFlag = 1) OR
(NOT (Ts.StatusEndDate = NULL)) AND
(NOT (Tj.JobEndDate = NULL)) AND
(NOT (Tl.LocationEndDate = NULL))

 

[Cagliostro]

try following:
SELECT DISTINCT
E.EmployeeName, Tj.JobTitle, Tl.LocationCode,Ts.StatusCode
FROM
Table_Employee E INNER JOIN Table_Status Ts
ON
E.EmployeeID = Ts.EmployeeID INNER JOIN Table_Job Tj
ON
Ts.EmployeeID = Tj.EmployeeID INNER JOIN Table_Location Tl
ON
Ts.EmployeeID = Tl.EmployeeID AND
Tj.EmployeeID = Tl.PEmployeeID
where
(
E.EmployeeID in(select EmployeeID from Ts where Ts.StatusCode = 'Terminated')
)and
(
(
(Ts.StatusCurrentFlag = 1) AND
(Tj.JobCurrentFlag = 1) AND
(Tl.LocationCurrentFlag = 1)
)OR
(
(Ts.StatusEndDate is not null) AND
(Tj.JobEndDate is not null) AND
(Tl.LocationEndDate is not null)
)
)

 

[Deam]

It is returning multiple rows for some employees (who have more than one Location record)!! That was my problem in the first place. Is there something else that can be used in place of DISTINCT ?

 

[Andel]

could you provide sample tables with data and a query result that you want? Andel
maingel@hotmail.com

 

[Cagliostro]

Deam, is up to you wich one to choose from a multiple returning. I don't understand what is your problem. John Fill