Joining tables based on max value

[emozley]

Hi,

I have two tables Users and Salaries in an Access 2003 database.

The Users table contains UserId, FirstName and Surname.
The Salaries table contains UserID, ReviewDate, Salary.

What I want to do is to join the tables but only show the salary with the most recent review date for each member of staff.

If there isn't a matching row in the salaries table can the query still return the name and the columns with review date and salary show as blank or will it hide the name altogether? I would want all names to show regardless.

Thanks very much

Ed

 

[jrbarnett]

Something like:

Select Users.UserID, Users.Firstname, Users.Surname, Max (Salaries.Reviewdate) As ReviewDate, 
 (select Salary From Salaries S where Salaries.Userid = S.Userid and Salary.ReviewDate = S.ReviewDate) As Salary
From Users Left Join Salaries On Users.Userid = Salaries.UserID

John

 

[lespaul]

Another option:

SELECT U.UserID, U.FirstName, U.SurName, S.Salary, S.MaxReview FROM
USERS U
LEFT OUTER JOIN (SELECT UserID, Salary, Max(ReviewDate) As MaxReview FROM Salaries GROUP BY UserID, Salary) S ON U.UserID = S.UserID

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[lespaul]

Actually that should be:

SELECT U.UserID, U.FirstName, U.SurName, S.Salary, S.MaxReview FROM
USERS U
LEFT JOIN (SELECT UserID, Salary, Max(ReviewDate) As MaxReview FROM Salaries GROUP BY UserID, Salary) S ON U.UserID = S.UserID

 

[emozley]

Hi,

It's coming up with an error:

Cannot find the input table or query 'SELECT UserID, Salary, Max(ReviewDate) As MaxReview FROM Salaries GROUP BY UserID, Salary'. Make sure that it exists and that its name is spelled correctly.

Not sure how to fix this...

Thanks!

Ed

 

[lespaul]

I would try saving that as it's own query: qMaxSalaries and then modifying the main query to:

SELECT U.UserID, U.FirstName, U.SurName, S.Salary, S.MaxReview FROM
USERS U
LEFT JOIN qMaxSalaries S ON U.UserID = S.UserID

I know that you can use the 'in-line' queries in Access 2000 and above for INNER JOINs, I guess they don't work for LEFT JOINs?

Maybe one of the other MVPS can enlighten us...

Leslie