Problem in creating the query containing a CROSS JOIN followed by a LEFT JOIN in Microsoft Access

Question

I'm trying to convert the following SQLite query to a MS-ACCESS (Microsoft Access 2016) query without any success:

query in SQLite:

SELECT c.CategoryName, s.SubCategoryName, COUNT(l.LoanDate) AS Count
FROM LoanCategories c CROSS JOIN LoanSubCategories s
LEFT JOIN Loans l
ON l.LoanCatId = c.CategoryId AND l.LoanSubCatId = s.CategoryId AND l.LoanDate = '19990125'
GROUP BY c.CategoryName, s.CategoryName;

I've tried the following query in MS-ACCESS which leads to the 'Syntax error in JOIN operation' error:

my query in MS-ACCESS:

SELECT c.CategoryName, s.SubCategoryName, COUNT(l.LoanDate) AS Count
FROM LoanCategories c, LoanSubCategories s
LEFT JOIN Loans l
ON l.LoanCatId = c.CategoryId AND l.LoanSubCatId = s.CategoryId AND l.LoanDate = '19990125'
GROUP BY c.CategoryName, s.CategoryName;

How could I rewrite the query in MS-ACCESS?

Answer 1

The syntax in MS-Access is quite different.

The explicit CROSS join is not supported and you must use the old comma-syntax.

Also, the ON clause of a join can't contain expressions like LoanDate = '19990125'.

The query could be written like this:

SELECT t.CategoryName, t.SubCategoryName, Nz(l.Count, 0) AS Count
FROM (
  SELECT c.CategoryId, c.CategoryName, 
         s.CategoryId AS SubCategoryId, s.CategoryName AS SubCategoryName
  FROM LoanCategories AS c, LoanSubCategories AS s
) AS t
LEFT JOIN (
  SELECT LoanCatId, LoanSubCatId, COUNT(*) AS Count
  FROM Loans
  WHERE LoanDate = '19990125'
  GROUP BY LoanCatId, LoanSubCatId
) AS l ON l.LoanCatId = t.CategoryId AND l.LoanSubCatId = t.SubCategoryId;