CODEWITHSUNDEEP
ms-access
T4mer
T4mer

Reputation: 440

ms access query error

I'm converting my database from SQL Server to MS Access.

This query works fine in SQL Server, but it has syntax error in MS-Access.

So what is wrong with this SQL statement??

select * 
from Students 
left join (select Lessons.StudentID, COUNT(*) as LessonsCount 
           from Lessons 
           group by Lessons.StudentID) as c on c.StudentID = Students.ID  
left join (select Tests.StudentID, count(*) as TestsCount 
           from Tests 
           group by Tests.StudentID) as dd on dd.StudentID = c.StudentID

Upvotes: 1

Views: 260

Answers (1)

HansUp
HansUp

Reputation: 97101

Access SQL requires parentheses with multiple joins. The fail-safe way to deal with that issue is to set up your joins in the query designer, and let Access add the parentheses it wants.

It may come out looking something like this:

select * 
from (Students 
left join [select Lessons.StudentID, COUNT(*) as LessonsCount 
           from Lessons 
           group by Lessons.StudentID]. as c on c.StudentID = Students.ID) 
left join [select Tests.StudentID, count(*) as TestsCount 
           from Tests 
           group by Tests.StudentID]. as dd on dd.StudentID = c.StudentID

A side effect with the query designer is that it tends to convert subqueries from this form

(SELECT some_field FROM some_table) AS sub

to this ...

[SELECT some_field FROM some_table]. AS sub

Upvotes: 3

Related Questions