Select query question

[TSSTechie]

Howdy

I have a select query with the following SQL statement:

SELECT Staff.[ID Name], Staff.Surname, Staff.Forename, Staff.[Employee Number], Staff.[Current Position], Training.DateCompleted, Training.DayUnknown, Training.MonthUnknown
FROM Staff INNER JOIN Training ON Staff.[ID Name]=Training.IDName
WHERE (((Training.CourseTitle)=Forms!ViewManTrain!ManTrainTitle));

Some staff will have completed the chosen training course (CourseTitle) more than once, on different dates. What I would like is for the query to show only the most recent occurence of the course for each member of staff.

I tried adding the word DISTINCT after SELECT but this had no effect.

I would be very grateful if someone could offer a solution to this.

Thanks in advance

TSSTechie

May The Force Be With You

 

[Golom]

SELECT Staff.[ID Name], Staff.Surname, Staff.Forename,
       Staff.[Employee Number], Staff.[Current Position],
       MAX(Training.DateCompleted) As [Last Completed],
       Training.DayUnknown, Training.MonthUnknown

FROM Staff INNER JOIN Training ON Staff.[ID Name]=Training.IDName

WHERE (((Training.CourseTitle)=Forms!ViewManTrain!ManTrainTitle))

Group By Staff.[ID Name], Staff.Surname, Staff.Forename,
         Staff.[Employee Number], Staff.[Current Position],  
         Training.DayUnknown, Training.MonthUnknown;

I don't know what the fields Training.DayUnknown, Training.MonthUnknown are about and you may need to set up aggregate functions for them as well.

 

[PHV]

Another way:
SELECT S.[ID Name], S.Surname, S.Forename, S.[Employee Number], S.[Current Position], T.DateCompleted, T.DayUnknown, T.MonthUnknown
FROM Staff S INNER JOIN Training T ON S.[ID Name]=T.IDName
WHERE T.CourseTitle=Forms!ViewManTrain!ManTrainTitle
AND T.DateCompleted=(SELECT Max(M.DateCompleted) FROM Training M WHERE M.IDName=T.IDName AND M.CourseTitle=T.CourseTitle);

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

 

[TSSTechie]

Howdy

Thanks very much for the help guys.

Golom
I think you might be right about setting up aggregate functions for dayunknown and month unknown (tick boxes by the way) as the query still doesn't work unless I remove those 2 fields altogether, then it works.

PHV
Don't really understand why S & T work but they do. Works brilliantly, thanks a lot.

Thanks again both of you

TSSTechie

May The Force Be With You

 

[PHV]

Don't really understand why S & T work
S & T are simply aliases for Staff and Training respectively.

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