sql - 字段名称似乎不会延续到交叉表查询中

Question

我有一个查询链——数据从一个表到一个选择查询，然后到一个联合查询，然后是另一个选择查询，最后到一个交叉表。每个查询在每个步骤都有效，直到我到达交叉表。那时，我收到一个错误，即原始表中的字段是必需的但不可用。

这是查询链：

第一个选择查询，称为HSICInfantsSummary

 SELECT 
      Dates.[Report type], Dates.Start, Dates.End, 
      HSICInfantsReport.CHILD_CONTACT_ID AS ID, 
      .....
   FROM ((HSICInfantsReport 
       INNER JOIN HSInterconceptionReport ON HSICInfantsReport.CHILD_CONTACT_ID = HSInterconceptionReport.CLIENTUNIQUEIDENTIFICATION)
       INNER JOIN HSDemographicsReport ON HSICInfantsReport.CHILD_CONTACT_ID = HSDemographicsReport.CONTACT_ID)
       INNER JOIN (Dates INNER JOIN [Active client list] ON Dates.[Report type] = [Active client list].[Report type]) ON HSICInfantsReport.CHILD_CONTACT_ID = [Active client list].ClientID;

这进入一个名为Insurance Detail的联合查询：

select "Client" as type, CLIENTUNIQUEIDENTIFICATION as ID, Admin_Date, Insurance from HSInterconceptionReport 
union all select "Client", CONTACT_ID, admin_date, insurance from HSPPScreeningReport 
union all select "Client", CONTACT_ID, admin_date, insurance from HSPreconceptionReport 
union all select "Client", CLIENTUNIQUEIDENTIFICATION, AdminDate, HealthInsuranceTypesIDs from HSPrenatalScreeningReport
union all select "Infant", format(ID) & " - " & format(DOB) & " - " & birthorder, Admin_date, BabyInsurance  from HSICInfantsSummary 
UNION ALL select "Infant", format(ID) & " - " & format(DOB & " - " & birthorder), Admin_Date, BBYInsurance from HSPPInfantsSummary;

这进入另一个名为Insurance Summary - client的选择查询：

SELECT  
  [All participants summary].[report type],
  [Insurance detail].type, 
  [Insurance detail].ID, 
  .....
FROM [Insurance detail] 
  INNER JOIN [All participants summary] ON [Insurance detail].ID = [All participants summary].ID
GROUP BY  
  [All participants summary].[report type],
  [Insurance detail].type, 
  [Insurance detail].ID, 
  ....
HAVING ((([Insurance detail].type)="Client") AND (([Insurance detail].Admin_Date)=(select max(admin_date) from [insurance detail] T where T.id=[insurance detail].id)))
ORDER BY [Insurance detail].ID, [All participants summary].EnrolledDate;

此查询工作正常并给出了预期的结果。然后我想做一个数据列的交叉表：

TRANSFORM Count([Insurance summary - client].ID) AS CountOfID  
SELECT [Insurance summary - client].[report type]  
FROM [Insurance summary - client]  
GROUP BY [Insurance summary - client].[report type]  
PIVOT [Insurance summary - client].Insured;

这就是它崩溃的地方。这个查询应该知道来自 Insurance summarry - client 的字段“ID”，但它似乎想一直返回到作为 ID 的原始来源的表，并且找不到它需要的东西。错误信息是：

你能告诉我为什么会发生这种情况，更重要的是如何解决它？

标签： sqlms-access