MS Access query syntax

Question

Here's an MS Access query that returns data:

SELECT Invoices.ID, Invoices.DateGenerated, Timesheet.ActivityDate, Timesheet.ActivityDescription, 
Timesheet.Hours, Rate.GstExcRate, Rate.GSTRate, Timesheet.Hours * Rate.GstExcRate AS BilledGSTExc, 
Timesheet.Hours * Rate.GstRate AS BilledGST
FROM 
(Invoices INNER JOIN Timesheet ON Invoices.ID = Timesheet.InvoiceID) 
INNER JOIN Rate 
ON (Timesheet.ActivityDate >= Rate.DateFrom) AND (Timesheet.ActivityDate <= Rate.DateTo);

I need to add another table so I add another INNER JOIN to this:

SELECT Invoices.ID, Invoices.DateGenerated, Timesheet.ActivityDate, Timesheet.ActivityDescription, 
Timesheet.Hours, Rate.GstExcRate, Rate.GSTRate, Timesheet.Hours * Rate.GstExcRate AS BilledGSTExc, 
Timesheet.Hours * Rate.GstRate AS BilledGST
FROM 
(Invoices INNER JOIN Timesheet ON Invoices.ID = Timesheet.InvoiceID) 
INNER JOIN Rate 
ON (Timesheet.ActivityDate >= Rate.DateFrom) AND (Timesheet.ActivityDate <= Rate.DateTo)
INNER JOIN Periods 
ON (Periods.ID = Invoices.PeriodID);

but I get

Syntax error (missing operator) in query expression '(Timesheet.ActivityDate >= Rate.DateFrom) AND (Timesheet.ActivityDate <= Rate.DateTo) INNER JOIN Periods ON (Periods.ID = Invoices.PeriodID

Clearly there is some specific MS Access syntax required here. Reading I've done implies that INNER JOINS need to be 'nested' but official doco says it's optional.

I've rearranged it in various ways but I can't crack it.

Can anyone tell me is wrong with this syntax (it's perfectly valid in every other SQL dialect that I've used)

score 0 · Answer 1 · answered Jan 04 '20 at 11:17

OK based on this:

How do I join 4+ tables in Access with a combination inner/outer?

I changed it to this:

SELECT Invoices.ID, Invoices.DateGenerated, Timesheet.ActivityDate, Timesheet.ActivityDescription, 
Timesheet.Hours, Rate.GstExcRate, Rate.GSTRate, Timesheet.Hours * Rate.GstExcRate AS BilledGSTExc, 
Timesheet.Hours * Rate.GstRate AS BilledGST
FROM 
(
  (
    (Invoices INNER JOIN Timesheet ON Invoices.ID = Timesheet.InvoiceID) 
    INNER JOIN Rate 
    ON (Timesheet.ActivityDate >= Rate.DateFrom) AND (Timesheet.ActivityDate <= Rate.DateTo)
  )
  INNER JOIN Periods 
  ON (Periods.ID = Invoices.PeriodID)
);

Which functions correctly.

GMB · Accepted Answer · 2020-01-04T12:42:16.143

0

Access want parentheses for multi-joins, so:

SELECT 
    i.ID,  
    i.DateGenerated, 
    t.ActivityDate, 
    t.ActivityDescription, 
    t.Hours, 
    r.GstExcRate, 
    r.GSTRate, 
    t.Hours * r.GstExcRate AS BilledGSTExc, 
    t.Hours * r.GstRate AS BilledGST
FROM 
    (
        (
            Invoices i 
            INNER JOIN Timesheet t
                ON i.ID = t.InvoiceID
        ) 
        INNER JOIN Rate r
            ON (t.ActivityDate >= r.DateFrom) AND (t.ActivityDate <= r.DateTo)
    )
    INNER JOIN Periods p
        ON (p.ID = i.PeriodID)

You might also want to use table aliases to shorten your query.

edited Jan 04 '20 at 12:42

answered Jan 04 '20 at 11:18

GMB

216,147
25
84
135

This one returns _Syntax error in JOIN operation_. That's a good tip about the aliases – Nick.Mc Jan 04 '20 at 12:28
@Nick.McDermaid: there was a typo on one of the aliases that I just fixed, does it work now? – GMB Jan 04 '20 at 12:43

MS Access query syntax

2 Answers2