none
Query to return TOP 1 date for each group RRS feed

  • 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

    Wednesday, January 10, 2018 1:36 PM

Answers

  • 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

    Wednesday, January 10, 2018 6:38 PM

All replies

  • 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

    Wednesday, January 10, 2018 6:38 PM
  • Ken as always you're my hero! Thanks a stack
    Wednesday, January 10, 2018 8:00 PM