MS Access SQL中如何关联两查询结果并实现一对多合并查询？

需求概述 #

• 现有两个MS Access SQL查询，需合并为单个查询，后续用于创建支持按pathid、ctcode或casetype输入筛选条件的表单
• 核心痛点：MS Access不支持GROUP_CONCAT()函数，无法实现一对多结果展示（一个pathid对应一个casenumber，一个casenumber对应多个ctcode）
• 涉及数据表：patcase、billingcode、internalbillingcode、consultinformation、specimen

现有查询代码 #

内查询（获取pathid、casenumber和ctcode的一对多结果） #

注：原内查询代码以图片形式提供，暂无法获取具体SQL内容

外查询（基于内查询结果，筛选含非空接收日期、签出日期及周转时间的记录，支持pathid筛选） #

SELECT DISTINCT
        PATCASE.PATHID,
        PATCASE.CASENUMBER, 
        INTERNALBILLINGCODE.CTCODE
FROM 
     (
       PATCASE 
       INNER JOIN 
       BILLINGCODE 
       ON 
       PATCASE.AAID = BILLINGCODE.RACC_BILL
     ) 
       INNER JOIN 
       INTERNALBILLINGCODE 
       ON 
       BILLINGCODE.CODE = INTERNALBILLINGCODE.IBCODE
WHERE
       PATCASE.PATHID = 'DMP'
AND
       PATCASE.CASENUMBER LIKE 'BMAC3*'
AND
       PATCASE.CASENUMBER LIKE '*118';

SELECT 
       MAX(Nz(PATCASE.PATHID, '')) AS PATHID,
       MAX(Nz(PATCASE.CASENUMBER, '')) AS CASENUMBER,
       MAX(Nz(PAT_INTERNALBILLINGCODE.CTCODE, '')) AS CPTCODE, 
       MAX(Nz(CONSULTMATINFORMATION.CONSULTATNTID, '')) AS CONSULT_CODE, 
       MAX(Nz(CONSULTMATINFORMATION.MATERIALSENDDATE, '')) AS MATERIAL_SEND_DATE,

Format(
        MAX(
            IIF(
                 [RECEIVEDDATE] = 0, 
                 NULL, 
                 DateSerial(Left(Nz([RECEIVEDDATE], '0000'), 4), Mid(Nz([RECEIVEDDATE], '0000'), 5, 2),
                 Right(Nz([RECEIVEDDATE], '00'), 2))
                )
            ),
              'Short Date'
      ) AS RECEIVE_DATE,

Format(
        MAX(
            IIF(
                [SIGNOUTDATE] = -1, 
                NULL, 
                DateSerial(Left(Nz([SIGNOUTDATE], ''), 4), Mid(Nz([SIGNOUTDATE], ''), 5, 2), 
                Right(Nz([SIGNOUTDATE], ''), 2))
               )
            ),
              'Short Date'
      ) AS SIGNOUT_DATE,

Format(
       MAX(
           IIF(
                [RECEIVEDDATE] = 0 OR [SIGNOUTDATE] = -1, 
                NULL, 
                DateDiff('d', 
                DateSerial(Left(Nz([RECEIVEDDATE], ''), 4), Mid(Nz([RECEIVEDDATE], ''), 5, 2),
                Right(Nz([RECEIVEDDATE], ''), 2)), 
                DateSerial(Left(Nz([SIGNOUTDATE], ''), 4), Mid(Nz([SIGNOUTDATE], ''), 5, 2), 
                Right(Nz([SIGNOUTDATE], ''), 2))
              )
           )
        ),
          '0'
  ) AS TURNAROUND_TIME
FROM
     (
       (
         (
           PATCASE
           INNER JOIN BILLINGCODE 
           ON 
           PATCASE.AA_ID = BILLINGCODE.RACC_BILL
         )     
        INNER JOIN INTERNALBILLINGCODE 
        ON 
        BILLINGCODE.CODE = INTERNALBILLINGCODE.IBCODE
       )
      INNER JOIN CONSULTMATINFORMATION 
      ON 
      PATCASE.CASENUMBER = CONSULTMATINFORMATION.CASENUMBER
     )
    INNER JOIN SPECIMEN 
    ON 
    SPECIMEN.CASENO = PATCASE.CASENUMBER 
WHERE 
    PATCASE.PATHID = 'DMP'
    GROUP BY 
    PATCASE.CASENUMBER 
    HAVING 
    MAX(Nz(PATCASE.PATHID, '')) <> '';

疑问与尝试 #

• 不确定当前查询的实现逻辑和语法是否正确，倾向于用内查询获取pathid和casenumber，也可调整为基于外查询的结构
• 尝试将内查询封装为函数但无操作思路，使用GROUP_CONCAT()时Access直接报错
• 作为Access新手，纠结选择VBA还是MS SQL实现需求：了解到两者语法不同，VBA需每次调用执行，SQL则是预设好的查询语句

内容的提问来源于stack exchange，提问作者Mike