Microsoft Access 2007 - querying mulitple tables produces errors

Question

I am trying to create a query in MS Access that queries up to 4 tables to produce a nice collation of information.
Upon clicking the view I receive an error:

Syntax error (missing operator) in query expression sessions.sessionTypeFK=session_type.session_type.id) inner join staff on (sessions.StaffFK=staff.staff_id) inner join person on (staff.FK_personID=person.personID'

Interestingly it always misses off the last bracket in error?

SELECT sessions.*, session_type.[Session Type],forename,surname
from sessions
inner join session_type on (sessions.SessionTypeFK=session_type.session_type_id)
inner join staff on (sessions.StaffFK=staff.Staff_ID)
inner join person on (staff.FK_PersonID=person.PersonID)

I know that MS Access is fuzzy, and you have to wrap the inner join with () to get it to work, I have done this with several other queries but am genuinely struggling to get this bit parsed.

score 0 · Accepted Answer · answered Jan 07 '12 at 23:31

0

Do you mean:

SELECT sessions.*, session_type.[Session Type],forename,surname
from ((sessions
inner join session_type on sessions.SessionTypeFK=session_type.session_type_id)
inner join staff on sessions.StaffFK=staff.Staff_ID)
inner join person on staff.FK_PersonID=person.PersonID

Why not use the Access query design window and then switch to SQL view to get sample SQL?

answered Jan 07 '12 at 23:31

Fionnuala

90,370
7
114
152

Thank you very much. It would be so much easier if microsoft sticked to the standards instead if inventing their own. – Trabumpaline Jan 08 '12 at 17:24

score 0 · Answer 2 · answered Jan 08 '12 at 00:30

0

As they are all inner joins, you could re-write the query in a more simple format without using the query designer to look like this:

SELECT S.*, T.[Session Type], P.Forename, P.Surname
FROM Sessions As S, Session_Type As T, Staff As F, Person As P
WHERE T.Session_Type_ID = S.SessionTypeFK
AND F.Staff_ID = S.StaffFK
AND P.PersonID = F.FK_PersonID

As long as you are not working with outer joins then you can link your tables in the WHERE clause so it's easy to read back. You may also note I have used Aliases for the table names, this can also help make the SQL easier to follow and can significantly shrink down the code if you are writing a large SQL or have long tablenames.

answered Jan 08 '12 at 00:30

Matt Donnan

4,933
3
20
32

I am not sure that implicit joins are a good idea, consider "... has the unwanted possibility of a cross join: you can add tables to the FROM part without corresponding WHERE clause. This is considered harmful." -- http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins – Fionnuala Jan 08 '12 at 13:15
@Remou Fair point, although writing SQL in this way will hopefully encourage people to become SQL competent (apart from just the query designer as it can be unefficient in some cases) and learn about the practices not to use. – Matt Donnan Jan 08 '12 at 15:52

Microsoft Access 2007 - querying mulitple tables produces errors

2 Answers2