Search code examples
sqldatabasems-access

MS Access Query SQL

SELECT PEOPLE2257.Person Id, PEOPLE2257.Name, PEOPLE2257.Gender, RESULTS2257.Minutes, RESULTS2257.Event
FROM PEOPLE2257 INNER JOIN RESULTS2257 ON PEOPLE2257.[Person Id] = RESULTS2257.Person;
(WHERE RESULTS2257.Minutes > ((SELECT AVG(RESULTS2257.Minutes) FROM RESULTS2257 WHERE RESULTS2257.Event = 2;))

What's wrong with this?

Solution

  • You have have several issues with the query. Try this version:

    SELECT p.[Person Id], p.Name, p.Gender, r.Minutes, r.Event
FROM PEOPLE2257 AS p INNER JOIN
     RESULTS2257 AS r
     ON p.[Person Id] = r.Person;
WHERE r.Minutes > (SELECT AVG(R2.Minutes)
                   FROM RESULTS2257 AS R2 
                   WHERE R2.Event = 2
                  );

    The issues are:

    • Person Id in the SELECT has a space.
    • No open paren around the WHERE clause.
    • No semicolon in the middle of a query.
    • An extra closing parent.

    I also added table aliases so the query is easier to write and to read.