This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(6.4, 40%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHK%3C/text%3E%3C/svg%3E)
I am struggling with an access database. I cannot figure this out. I keep getting a "Syntax error in JOIN operation" error. Can someone please help me? Below is the SQL data.
SELECT
Courses.course_name
,Venues.venue_name
,Students.student_name
,Assessors.assessor_name
,Bookings.booking_date
FROM (Bookings
INNER JOIN (Courses
ON Bookings.course_id = Courses.course_id)
INNER JOIN (Venues
ON Bookings.venue_id = Venues.venue_id)
INNER JOIN (Students
ON Bookings.student_id = Students.student_id)
INNER JOIN (Assessors
ON Bookings.assessor_id = Assessors.assessor_id)
WHERE Bookings.booking_date BETWEEN #2025-01-19# AND #2025-01-25#
ORDER BY Courses.course_name, Venues.venue_name, Students.student_name, Assessors.assessor_name;
Trying remove the parentheses. They seem to be in the wrong place.
Unfortunately that didn't work
This runs without syntax error on SQL Server, and I expect it to run on Access as well, with the exception of the delimiters around the dates. (Which I had to change to fit the syntax on SQL Server.)
SELECT
Courses.course_name
,Venues.venue_name
,Students.student_name
,Assessors.assessor_name
,Bookings.booking_date
FROM Bookings
INNER JOIN Courses
ON Bookings.course_id = Courses.course_id
INNER JOIN Venues
ON Bookings.venue_id = Venues.venue_id
INNER JOIN Students
ON Bookings.student_id = Students.student_id
INNER JOIN Assessors
ON Bookings.assessor_id = Assessors.assessor_id
WHERE Bookings.booking_date BETWEEN '2025-01-19' AND '2025-01-25'
ORDER BY Courses.course_name, Venues.venue_name, Students.student_name, Assessors.assessor_name;
I then recieved this error message after trying this.
Queries in Access are frequently intolerant of multiple joins without nested parentheses. I'm horrible at sussing out where they go in an Access query without source tables.
That said, try this:
SELECT
Courses.course_name
,Venues.venue_name
,Students.student_name
,Assessors.assessor_name
,Bookings.booking_date
FROM
(Bookings
INNER JOIN
(((Courses
ON Bookings.course_id = Courses.course_id)
INNER JOIN Venues
ON Bookings.venue_id = Venues.venue_id)
INNER JOIN Students
ON Bookings.student_id = Students.student_id)
INNER JOIN Assessors
ON Bookings.assessor_id = Assessors.assessor_id)
WHERE Bookings.booking_date BETWEEN '2025-01-19' AND '2025-01-25'
ORDER BY Courses.course_name, Venues.venue_name, Students.student_name, Assessors.assessor_name;
If you do get additional syntax errors, you need to move the nested parentheses accordingly. Unfortunately, because I never invested time in working out the arcane rules Access uses for nesting parentheses, I usually resort to the Query Designer to get them right.
I then recieved this error message after trying this.
That's what you get for adding the SQL Server tag to your question. (Which I have since removed). You get answers from SQL Server people who want to be helpful, but who nothing about Access. I must say that I'm surprised that Access don't grok plain standard ANSI syntax.
Oh well, I'll crawl back under my rock.
Try to remove all ( and ).
Dint work unfortunately, anything else?
That didnt work unfortuately, anything else?
Use Access (Create, Query Design) to create and test a new valid query.
Try this:
FROM (((Bookings
INNER JOIN Courses
ON Bookings.course_id = Courses.course_id)
INNER JOIN Venues
ON Bookings.venue_id = Venues.venue_id)
INNER JOIN Students
ON Bookings.student_id = Students.student_id)
INNER JOIN Assessors
ON Bookings.assessor_id = Assessors.assessor_id
says ''Syntax error from CLAUSE''
i mean FROM clause
I don't understand why that should raise an error. The following query is from one of my own files. As you can see the nested parenthesizing of the JOIN clauses is as in my first reply:
SELECT PatientPrescriptions.PatientID,PatientPrescriptions.DatePrescribed,
Drugs.DrugName,DrugForms.DrugForm,Strengths.Strength,Units.Unit
FROM (((PatientPrescriptions
INNER JOIN Drugs ON PatientPrescriptions.DrugName = Drugs.DrugName)
INNER JOIN DrugForms ON PatientPrescriptions.DrugForm = DrugForms.DrugForm)
INNER JOIN Strengths ON PatientPrescriptions.Strength = Strengths.Strength)
INNER JOIN Units ON PatientPrescriptions.Unit = Units.Unit;
This is the generic syntax for Access SQL:
SELECT
Table1.FieldA, Table1.FieldB, Table2.FieldC <etc ..>
FROM Table1
INNER JOIN ((Table2
INNER JOIN Table3 ON Table2.ID = Table3.FK)
INNER JOIN Table4 ON Table3.ID = Table4.FK)
ON Table1.ID = Table2.FK;