时间戳

首页 > 解决方案 > MS Access 查询语法

sql - MS Access 查询语法

问题描述

这是一个返回数据的 MS Access 查询：

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：

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);

但我明白了

查询表达式中的语法错误（缺少运算符）'(Timesheet.ActivityDate >= Rate.DateFrom) AND (Timesheet.ActivityDate <= Rate.DateTo) INNER JOIN Periods ON (Periods.ID = Invoices.PeriodID

显然这里需要一些特定的 MS Access 语法。阅读我所做的意味着 INNER JOINS 需要“嵌套”，但官方文档说它是可选的。

我已经以各种方式重新排列它，但我无法破解它。

谁能告诉我这个语法有问题（它在我使用过的所有其他 SQL 方言中都完全有效）

标签： sqlms-accessjoin

解决方案

好的，基于此：

如何使用内部/外部组合在 Access 中加入 4 个以上的表？

我把它改成这样：

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)
);

哪个功能正确。

推荐阅读