"Syntax error in from clause" with nested query - MS Access 2016

Question

Here is my query:

SELECT
    t2.*
FROM
(
    SELECT
        FullName
    FROM
        pr
    GROUP BY
        FullName
    HAVING
        COUNT(*)>=2
) T1
JOIN
    pr T2 ON T1.FullName = T2.FullName;

I used this answer to build this: SQL Return only duplicate rows.

Although they didn't specify which DBMS they were using, I can assume it was not MS Access since it ran properly for them and not me.

Whenever I try to save this query, it says:

Syntax error in FROM clause

Not sure where this is wrong. I know that access requires some weird brackets during joins but the problem is apparently with one of the FROM statements. Any ideas here?

Gordon Linoff · Accepted Answer · 2018-06-07 19:01:54Z

2

I would expect an MS Access query to look like this:

SELECT t2.*
FROM (SELECT FullName
      FROM pr
      GROUP BY FullName
      HAVING COUNT(*) >= 2
     ) AS T1 INNER JOIN
     pr as T2
     ON T1.FullName = T2.FullName;

Note the ass and INNER.

answered Jun 7, 2018 at 19:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Over a year ago

It was the INNER that did it. Frustrating that Access doesn't give better debugging messages. The extra as went unneeded however.

Gordon Linoff Over a year ago

@Michael . . . The ass might depend on the version of MS Access.

Collectives™ on Stack Overflow

"Syntax error in from clause" with nested query - MS Access 2016

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related