查询未在MS Access中运行。(Join和From语句)

Question

SELECT
    DL.DESCRIPTION
    , DL.ASSIGNEE
    , DL.LASTASSIGNEE
    , LU.BARCODE
    , FORMAT(D.ai_created, 'yyyy-MM-dd HH') AS [DATE AND HOUR CREATED]
    , SUM(DL.QUANTITY) AS [ITEM COUNT]
    , DL.MATERIAL_ID
    , (ACT.firstname & ' ' & ACT.lastname) AS [INDUCTORNAME]
    , D.ID AS [DLID]
FROM ANT_WMSDEMAND AS D
INNER JOIN ANT_WMSDEMANDLINE AS DL ON DL.DEMAND_ID = D.ID
INNER JOIN ANT_WMSLOADUNIT AS LU ON LU.ID = DL.LOADUNIT_ID
INNER JOIN ANT_ACTOR AS ACT on ACT.name = LEFT(DL.AI_CREATEDBY, 7)
WHERE 
    D.ai_created between CDATE('2019-12-15 06:00:00') ABD CDATE('2019-12-15 17:30:00')
    AND D.type = 'ORDER'
    AND D.STATE = 'ACTIVATED'
    AND D.REPORTER = 'PICKING_STATION'
GROUP BY 
    DL.DESCRIPTION, 
    DL.ASSIGNEE, 
    DL.LASTASSIGNEE, 
    LU.BARCODE, 
    FORMAT$(D.ai_created, 'yyyy-MM-dd HH'), 
    DL.MATERIAL_ID, 
    (ACT.firstname &' ' & ACT.lastname), 
    D.ID
ORDER BY FORMAT(D.ai_created, 'yyyy-MM-dd HH') ASC

from语句和join语句需要有括号，但我还不能弄清楚如何使其正确工作。我尝试在查询构建器中执行此操作，但仍然失败。有什么想法吗？

Answer 1

当存在多个联接时，MS Access要求连续联接为surrounded by parentheses，因此您的查询应为：

SELECT
    DL.DESCRIPTION
    , DL.ASSIGNEE
    , DL.LASTASSIGNEE
    , LU.BARCODE
    , FORMAT(D.ai_created, 'yyyy-MM-dd HH') AS [DATE AND HOUR CREATED]
    , SUM(DL.QUANTITY) AS [ITEM COUNT]
    , DL.MATERIAL_ID
    , (ACT.firstname & ' ' & ACT.lastname) AS [INDUCTORNAME]
    , D.ID AS [DLID]
FROM 
    (
        (
            ANT_WMSDEMAND AS D INNER JOIN ANT_WMSDEMANDLINE AS DL ON
            DL.DEMAND_ID = D.ID
        )
        INNER JOIN ANT_WMSLOADUNIT AS LU ON 
        LU.ID = DL.LOADUNIT_ID
    )
    INNER JOIN ANT_ACTOR AS ACT ON 
    ACT.name = LEFT(DL.AI_CREATEDBY, 7)
WHERE 
    D.ai_created between #2019-12-15 06:00:00# AND #2019-12-15 17:30:00#
    AND D.type = 'ORDER'
    AND D.STATE = 'ACTIVATED'
    AND D.REPORTER = 'PICKING_STATION'
GROUP BY 
    DL.DESCRIPTION, 
    DL.ASSIGNEE, 
    DL.LASTASSIGNEE, 
    LU.BARCODE, 
    FORMAT(D.ai_created, 'yyyy-MM-dd HH'), 
    DL.MATERIAL_ID, 
    (ACT.firstname &' ' & ACT.lastname), 
    D.ID
ORDER BY 
    FORMAT(D.ai_created, 'yyyy-MM-dd HH') ASC

您的between语句中也有一个拼写错误：

D.ai_created between CDATE('2019-12-15 06:00:00') ABD CDATE('2019-12-15 17:30:00')

ABD应为AND。

这两个cdate表达式也可以写成文字日期时间值：

D.ai_created between #2019-12-15 06:00:00# AND #2019-12-15 17:30:00#