Syntax Error in From when using nested Inner Joins in Access Query

Question

In using the following Query (based on Microsoft SQL format guides, as I am new to Access, though experienced with SQL) I get an error:

SELECT SW.USGS_NO, Q.SampleDate
FROM SW_PROPERTIES SW 
Inner Join (Locations L 
Inner join [(]Sample_Point P [
Inner Join [(]T_TestEvents E [
Inner Join [(]T_WQData Q )]
on E.TestEvent=Q.TestEvent)]
on P.SamplePnt=E.SamplePnt)]
on L.LocationPnt=P.LocationPnt)
on SW.SiteID=L.LocationPnt

The error is simply:

"Syntax error in FROM clause."

After closing the error, the renaming, or final, 'P' in "[(]SamplePoint P [" is highlighted.

I know the links between tables are valid, and I just want the data from the highest and lowest tables. What am I missing?

If I am drilling down through the tables, I only am really looking for data from the first and the last. Not from the tables in between that I have to link through. — Mike
– Mike, Commented Apr 3, 2013 at 17:31

Void Spirit · Accepted Answer · 2015-11-26 13:13:49Z

1

Try this

SELECT SW.USGS_NO, Q.SampleDate
FROM ((([SW_PROPERTIES] SW 
Inner Join ([Locations] L 
Inner join [Sample_Point] P 
Inner Join [T_TestEvents] E 
Inner Join [T_WQData] Q
on E.TestEvent=Q.TestEvent)
on P.SamplePnt=E.SamplePnt)
on L.LocationPnt=P.LocationPnt)
on SW.SiteID=L.LocationPnt

answered Nov 26, 2015 at 13:13

Void Spirit

9091 gold badge6 silver badges19 bronze badges

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

Comments

Raj More · Accepted Answer · 2013-04-03 17:19:49Z

0

What is the significance of the square brackets?

I removed them, and since you're just using INNER JOIN, you don't even need parenthesis. Try this:

SELECT 
    SW.USGS_NO, 
    Q.SampleDate

FROM SW_PROPERTIES SW 
Inner Join Locations L 
    on SW.SiteID=L.LocationPnt
Inner join Sample_Point P
    on L.LocationPnt=P.LocationPnt
Inner Join T_TestEvents E
    on P.SamplePnt=E.SamplePnt
Inner Join T_WQData Q 
    on E.TestEvent=Q.TestEvent

answered Apr 3, 2013 at 17:19

Raj More

48.2k34 gold badges139 silver badges199 bronze badges

2 Comments

Mike Over a year ago

Yeah, the reason I had to go on the Microsoft website to look up their SQL syntax was that I was getting errors using it that way. That is how I originally had it set up, but that gives a missing operator error. Apparently, MS likes all the brackets and parenthesis. Removing the brackets from the open paren. gives a Join syntax error, highlighting the last two Inner Joins. Removing all brackets seems to run, but asks for a Parameter Value for E.TestEvent. I'm not sure where it is getting the command to ask for a parameter...

grahamj42 Over a year ago

Access will always prompt for a parameter for an unrecognised fieldname. I find that Access usually requires brackets with multiple joins.

Collectives™ on Stack Overflow

Syntax Error in From when using nested Inner Joins in Access Query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related