ACCESS/SQL: inner join syntax

Question

Hello
I'm having problems making an inner join in access.

Could somebody correct my syntax ? I don't understand where the error comes from.

Tables:
AC (msn, reg)
failure (id, msn)

what I'm trying to do:
I want to list the number of failures by AC, displaying the ac.msn & .reg, and the number of failures for each of these.

Here is what I came up with:

SELECT  failure.msn, ac.reg, Count(failure.failid) AS failures
FROM Failure, AC
INNER JOIN AC
USING msn
GROUP BY failure.msn
ORDER BY Count(failure.failid)

but this returns "syntax error in FROM clause"

I don't understand why. What am I missing ?

Fenton · Accepted Answer · 2011-02-03 10:43:56Z

4

If you are doing the INNER JOIN, you don't need to also include the table in the FROM clause...

SELECT
    Failure.msn, 
    AC.reg, 
    COUNT(Failure.failid) AS failures
FROM
    Failure
INNER JOIN
    AC
ON
    AC.msn = Failure.msn
GROUP BY
    Failure.msn,
    AC.reg
ORDER BY
    3

answered Feb 3, 2011 at 10:43

Fenton

253k74 gold badges404 silver badges414 bronze badges

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

2 Comments

Christian M Over a year ago

works perfectly, thanks :) What does ORDER BY 3 means ? refers to the 3rd listing in the SELECT ?

Fenton Over a year ago

This just means order by the third column. Glad to help.

Collectives™ on Stack Overflow

ACCESS/SQL: inner join syntax

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related