Having trouble converting SQL query to Access

Question

I have this query which works fine in SQL Server, but not in Access, and I'm having trouble converting it. I've always heard that JET is missing some TSQL features, and I suppose complex joins is one of them.

SELECT C.[Position], TT.[Description] as TrainingType, T.ProgramTitle, T.ProgramSubTitle, T.ProgramCode, ET.CompletedDate
from HR_Curriculum C
LEFT JOIN HR_Trainings T ON C.TrainingID = T.TrainingID
LEFT JOIN HR_TrainingTypes TT ON T.TrainingTypeID = TT.TrainingTypeID

LEFT JOIN HR_EmployeeTrainings ET ON C.TrainingID = ET.TrainingID
                                 AND (ET.AvantiRecID IS NULL OR ET.AvantiRecID = '637')
where ( c.[Position] = 'Customer Service Representative'
      OR C.[Position] = 'All Employees')
order by [Position], Description, ProgramTitle

I tried putting the extra join clause down in the WHERE clause, but for some reason this does not yield the proper count of records.

Marc · Accepted Answer · 2012-10-15 18:17:15Z

2

When you have more than one JOIN in ms-access you need to wrap them with parenthesis like this:

SELECT C.[Position], TT.[Description] as TrainingType, T.ProgramTitle, T.ProgramSubTitle, T.ProgramCode, ET.CompletedDate
from (((HR_Curriculum C
LEFT JOIN HR_Trainings T ON C.TrainingID = T.TrainingID)
LEFT JOIN HR_TrainingTypes TT ON T.TrainingTypeID = TT.TrainingTypeID)

LEFT JOIN HR_EmployeeTrainings ET ON C.TrainingID = ET.TrainingID
                                 AND (ET.AvantiRecID IS NULL OR ET.AvantiRecID = '637'))
where ( c.[Position] = 'Customer Service Representative'
      OR C.[Position] = 'All Employees')
order by [Position], Description, ProgramTitle

or you will have the Missing Operator error

edited Oct 15, 2012 at 18:17

answered Oct 15, 2012 at 18:12

Marc

16.6k20 gold badges80 silver badges121 bronze badges

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

1 Comment

MAW74656 Over a year ago

Getting JOIN expression not supported, so I think you are right on about the parens, but maybe I can't filter in the join.

KFleschner · Accepted Answer · 2012-10-15 18:09:14Z

1

Check that your table alias are delacred with 'as'. Access doesn't like [tablename] [alias], instead try [tablename] as [alias]. I know the complex left joins shouldn't be a problem, but Access might be choking an the alias delcarations if it's returning some join error. I would also try quering out the restriction on the ET table, and then joining that to the larger query. I've noticed that trying to put restrictions on records involved in left or right joins will often not produce the correct records as Access will limit the set after the join.

answered Oct 15, 2012 at 18:09

KFleschner

4993 silver badges13 bronze badges

2 Comments

MAW74656 Over a year ago

And I'm also getting JOIN expression not supported. So I think perhaps I can't do any filtering in the join.

KFleschner Over a year ago

You should be able to do the filtering, but you have to be careful as Access will apply the filter after the join which may or may not be what you intended. Just to be on the safe side I would create queries for the filtered information and join those queries if needed.

Collectives™ on Stack Overflow

Having trouble converting SQL query to Access

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related