Thanks, sdkautoa
Code: Select all
DROP TABLE "Test" IF EXISTS;
Select
"ID",
"LastName",
"FirstName",
"#",
"Last_three_Average"
INTO "Test"
FROM
"GolferContactTBL"
RIGHT JOIN
(
SELECT
"GolferID",
COUNT( * ) as "#",
AVG( "GolferScore") "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
"LastName"