Multiple rows returned but I only want the last one.

[antihippy]

Hello,

I've sorted my query out toget me a bunch of records that are 16 months or older. Now I need to decide know which one is hhe last one. This is for a DB which records asessments. Thus you may have an entry for one user but he may have been assessed several times over the last few years.

So: Joh hSmith.

Assessment dats 1 (latest

Assessment dats 2

Assessment dats 3

Inly want asseessment date 1. Can anyone think of a way to do that?

 

[gol4]

see top property in sql or dmax()

 

[Remou]

Maybe something like:
[tt]SELECT DISTINCT (Select Max([AssessmentDate]) From [tblAssessments] A where A.[ID]=B.[ID]) AS MaxDate, B.[Name]
FROM [tblAssessments] AS B;[/tt]

 

[antihippy]

Thanks I'll check it out.

 

[antihippy]

Well I've been playing with these options. I couldn't get dmax to work so I tried just max instead. I'm going to post up some sql:

SELECT MEMBERS.[Membership Number], MEMBERS.[Approved for JBL], Max(T_CA.AssessmentDate) AS MaxOfAssessmentDate
FROM MEMBERS INNER JOIN T_CA ON MEMBERS.[Membership Number] = T_CA.[Membership Number]
GROUP BY MEMBERS.[Membership Number], MEMBERS.[Approved for JBL]
HAVING (((MEMBERS.[Approved for JBL]) Like "j" Or (MEMBERS.[Approved for JBL])="b" Or (MEMBERS.[Approved for JBL])="l") AND ((Max(T_CA.AssessmentDate))<DateAdd("m",-16,Now())))
ORDER BY MEMBERS.[Membership Number];

No I didn't nam,e the tables.

The problem is that I know that the results are not acurate. Without the max I get result sfor one member of:

Membership Approved Assessment
Number for JBL Date
000010 J 09/01/1996
000010 J 19/03/1998
000010 J 26/05/1998
000010 J 30/03/1999
000010 J 26/06/2000
000010 J 06/06/2002

With the max this member disappears. Clearly he has had an assessment that is over 16 months in the past (06/06/2002) and so it should should show as the mx value. Any ideas?

 

[Remou]

Hi
Maybe (?):

SELECT MEMBERS.[Membership Number], MEMBERS.[Approved for JBL], Max(T_CA.AssessmentDate) AS MaxOfAssessmentDate
FROM MEMBERS INNER JOIN T_CA ON MEMBERS.[Membership Number] = T_CA.[Membership Number]
WHERE (((T_CA.AssessmentDate)<DateAdd("m",-16,Now())))
GROUP BY MEMBERS.[Membership Number], MEMBERS.[Approved for JBL]
HAVING (((MEMBERS.[Approved for JBL]) Like "j" Or (MEMBERS.[Approved for JBL])="b" Or (MEMBERS.[Approved for JBL])="l"))
ORDER BY MEMBERS.[Membership Number];

 

[antihippy]

Thanks. Seems to do the trick. I think I was surffering from overload as your solution is ... simple!