Join expression not supported

Databases
1

My access database is giving me the error “Join expression not supported.” on the following query. Does that mean Access doesn’t support JOIN? I doubt it, so is it the syntax I’ve got wrong? I’m not great with SQL but I can usually get by and this has me really stumped.

SELECT Modules.Name, Invoices.Paid, COUNT(Tests.ID) AS Attempts, Purchases.Authorised FROM Invoices, Purchases, Products, Modules LEFT JOIN Tests ON Tests.ModuleID = Modules.ID GROUP BY Modules.Name, Invoices.Paid, Purchases.Authorised HAVING Modules.ID = Products.ModuleID AND Products.ID = Purchases.ProductID AND Purchases.InvoiceID = Invoices.ID AND Invoices.UserID = @UserID

2

Access supports JOINs just fine (as long as you remember to parenthesize them)

select Modules.Name
     , Invoices.Paid
     , COUNT(Tests.ID) AS Attempts
     , Purchases.Authorised
  from (((
       Invoices
inner
  join Purchases
    on Purchases.InvoiceID = Invoices.ID
       )
inner
  join Products
    on Products.ID = Purchases.ProductID
       )
inner
  join Modules
    on Modules.ID = Products.ModuleID
       )
left outer
  join Tests
    on Tests.ModuleID = Modules.ID
 where Invoices.UserID = @UserID
group
    by Modules.Name
     , Invoices.Paid
     , Purchases.Authorised
3 

SELECT Modules.Name,
       Invoices.Paid,
       COUNT(Tests.ID) AS Attempts,
       Purchases.Authorised
  FROM (((Invoices join Purchases on Purchases.InvoiceID = Invoices.ID)
       inner join Products on Products.ID = Purchases.ProductID)
       inner join modules on Modules.ID = Products.ModuleID)
       LEFT JOIN Tests ON Tests.ModuleID = Modules.ID
 where Invoices.UserID = @UserID
 GROUP BY Modules.Name,
       Invoices.Paid,
       Purchases.Authorised
4

Thanks for such a quick response, that works a treat!

I’m slowly getting my head round some of the more advanced features of SQL.

5