I am trying to create a report of all employees and their last login process code. I have three tables to work with: 1. Employee (PK EmployeeCode) 2. Production (PK Code-index key, assigned every time a new line is added to the table- not the employee code, although that field is part of the table structure)-lists all employees and all processes they clocked into for every day 3. ShopLogIn (PK EmployeeCode) - lists last time the employee clocked in with any process. My report needs to show: EmployeeName, EmployeeCode -Employee table LastLoginTime-ShopLogIn table or Production table LastProcess -Production Table

How can I get that? This is what I did so far, but it still shows me all processcodes the only thing that I accomplished is displaying the last time they clocked into some process, show for every process, per employeecode.

SELECT shoplogin.employeecode, shoplogin.loggeddate, shoplogin.loggedshift, shoplogin.entrytime, employee.employeename, employee.departmentcode, employee.plantid AS EmployeePlantID ,Process=( select top 1 a.ProcessCodefrom Production a inner join (select max(Code) as maxid from Production group by ProcessCode) as b on a.Code = b.maxid ) ,production.ProcessCode ,production.EntryTime ,production.Code FROM shoplogin INNER JOIN employee ON shoplogin.employeecode = employee.code left join Production on production.EmployeeCode=Employee.Code and ShopLogIn.LoggedDate=production.StartDate WHERE production.EmployeeCode=ShopLogIn.EmployeeCode

Try something like shown below. I may not have all the columns correctly, but the basic idea is the following:

1. You start with the Employee table and pick up all the columns you want.2. You use an OUTER APPLY to pick up just one row from the shoplogin table. Which row you pickup is determined by the order by clause in the outer apply query.3. Do the same thing for the produciton table.