[Solved] Avg the last 3 golf scores

[sdkautoa]

It's been at least 10 years since I done any type of coding so I'm kind of green around the gills.
So any help would be appreciated.
I'm trying to grab a max of the last 3 golf scores (some have only 1 or 2 scores inputted) to average per player.
GolferContactTBL With fields of (ID, LastName, FirstName), RoundTBL with fields of (GolferID, GolfDate,GolferScore,RecordNum). I have have the two tables linked with "ID" to "GolferID" as 1 to many relationship.
I'v been fighting with trying to make a query in both design mode and Sql with no luck. didn't think it should have been too hard to do but I'm hitting a brick wall.
Thanks Sdkautoa

[F3K Total]

Hello,
this should work:

Code: Select all

SELECT
    "LastName",
    "FirstName",
    "#",
    "Last_three_Average"
FROM
    "GolferContactTBL"
LEFT JOIN 
    (
    SELECT
        "GolferID",
        COUNT( * ) as "#",
        AVG( "GolferScore" * 1.000 ) "Last_three_Average"
    FROM
        (
        SELECT
            "GolferID",
            "GolfDate",
            "GolferScore"
        FROM
            "RoundTBL" "A"
        LEFT JOIN
            "RoundTBL" "B"
        ON
            "A"."GolfDate" <= "B"."GolfDate"
        AND
            "A"."GolferID" = "B"."GolferID"
        GROUP BY
            "GolferID",
            "GolfDate",
            "GolferScore"
        HAVING COUNT( * ) <= 3
        )
    GROUP BY
        "GolferID"
    ) AS Q3
ON 
    "GolferContactTBL"."ID" = "Q3"."GolferID"
ORDER BY
     "Last_three_Average"

R

[sdkautoa]

Wow... That was amazingly quick and I'v been hitting this for at least a week every day and getting nowhere fast. Thanks again F3K Total! I really appreciate your help. Yahooooo Now I can move on and hopefully build on this:-)

[F3K Total]

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
R