Query to return TOP 1 date for each group

Question

Hi Access Gurus it's been a while

I've tried several syntax's to get a result but I just can't get it right so I need your expertise to get me past this one. The query:

SELECT tblPtsPerCompHistory.PtsMale AS Dancer, tblPtsPerCompHistory.PtsSingle, tblCompetitions.Comp_Date, tblPtsPerCompHistory.PtsMale, [tblPtsPerCompHistory.PtsMale]
FROM tblPtsPerCompHistory INNER JOIN tblCompetitions ON tblPtsPerCompHistory.PtsCompID = tblCompetitions.Competition_Idx
WHERE (((tblPtsPerCompHistory.PtsSingle)=True) AND ((tblPtsPerCompHistory.PtsMale) Is Not Null And (tblPtsPerCompHistory.PtsMale)>0) AND (([tblPtsPerCompHistory.PtsMale]) In (SELECT TOP 1 Comp_Date
FROM [tblPtsPerCompHistory] as T
WHERE T.PtsMale = [tblPtsPerCompHistory].PtsMale)))
ORDER BY tblCompetitions.Comp_Date DESC

I need the latest (TOP 1) Comp_Date for each Dancer. Once I have that I can replicate the query for PtsFemale then combine in a Union query.

There are records that should be appearing but when I run this it takes a short while to change to datasheet view & returns zero records. Looking forward to finding out what I've been doing wrong.

Thanks in advance

Answer 1

Try this:

SELECT PPCH1.PtsMale AS Dancer,
PPCH1.PtsSingle, C1.Comp_Date
FROM tblPtsPerCompHistory AS PPCH1 INNER JOIN tblCompetitions AS C1
ON PPCH1.PtsCompID = C1.Competition_Idx
WHERE PPCH1.PtsSingle =TRUE
AND PPCH1.PtsMale > 0
AND C1.Comp_Date =
    (SELECT MAX(Comp_Date)
      FROM tblPtsPerCompHistory AS PPCH2 INNER JOIN tblCompetitions AS C2
      ON PPCH2.PtsCompID = C2.Competition_Idx
      WHERE PPCH2.PtsMale = PPCH1.PtsMale);

---

Ken Sheridan, Stafford, England

Answer 2

Ken as always you're my hero! Thanks a stack