Linking table

[maccaroo]

Hi guys. Thanks in advance for any advice. My problem involves 3
tables: Users, UserTypeLinks, UserTypes. The Users table essentially
has personal details, and the UserTypes table has rights data. The
UserTypeLinks table links these together by storing the primary key
from each of the other two tables, plus a date.


I need to create a query that will give me some personal details, plus
the LATEST link to the UserTypes table. Unfortunately, all I can think
to do is this:


SELECT Users.FirstName, Users.Surname, UserTypes.Type,
MAX(UserTypeLinks.DateChanged) FROM UserTypes INNER JOIN (Users INNER
JOIN UserTypeLinks ON Users.UserID = UserTypeLinks.UserID) ON
UserTypes.UserTypeID = UserTypeLinks.UserTypeID
GROUP BY Users.FirstName, Users.Surname, UserTypes.Type


This gives me a record for every Type, whereas I only want the Type
that goes with the latest Date. I realise why my query doesn't work
(I'm grouping by UserTypes.Type), but I can't remove it from the
grouping either...

 

[PHV]

Create a saved query named, say, qryLastType:
SELECT UserID, Max(DateChanged) AS LastDate
FROM UserTypeLinks
GROUP BY UserID

Now your query:
SELECT U.FirstName, U.Surname, T.Type, L.DateChanged
FROM (qryLastType Q
INNER JOIN Users U ON Q.UserID=U.UserID)
INNER JOIN (UserTypeLinks L INNER JOIN UserTypes T ON L.UserTypeID=T.UserTypeID
) ON Q.UserID=L.UserID AND Q.LastDate=L.DateChanged

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[maccaroo]

PHV, you're my hero! It works great. Thanks soooo much.