查询调用另外两个查询
查询调用另外两个查询
提问于 2013-10-05 05:20:34
我正在Access 2010数据库中编写一个查询，该查询调用同一Access 2010数据库中的另外两个查询。结果将是一个包含以下字段的表格报告：
FirstName, LastName, HomePhone, ClientNumber, Level, nextDate, Type 客户/行的列表由Query1生成，它还填充FirstName、LastName、HomePhone和客户编号。然后，Query2为上面第一个查询中定义的每个客户行填充Level、nextDate和Type。
到目前为止，我得到的摘要查询是：
SELECT Query1.FirstName, Query1.LastName, Query1.HomePhone, Query1.ClientNumber, Query2.[Level], Query2.nextDate, Query2.Type
FROM Query1
INNER JOIN Query2 ON Query1.ClientNumber=Query2.ClientNumber; 但是，当我尝试在数据表视图中查看此摘要查询的结果时，Access给出一个对话框，要求提供Query2.ClientNumber，这表明代码中的某些内容没有正确引用该变量。
作为参考，以下是Query1的代码：
SELECT ActiveCustomers.FirstName, ActiveCustomers.LastName, tblClientAddress.HomePhone, ActiveCustomers.ClientNumber
FROM
(
SELECT Clients.ClientNumber, Clients.FirstName, Clients.LastName
(
SELECT COUNT(ReferralDate) FROM IntakeTable WHERE Clients.ClientNumber = IntakeTable.ClientNumber AND Len(ReferralDate & '') > 0
) AS IntakeCount,
(
SELECT COUNT(ExitDate) FROM ExitTable WHERE Clients.ClientNumber = ExitTable.ClientNumber AND Len(ExitDate & '') > 0
) AS ExitCount
FROM Clients
) AS ActiveCustomers
INNER JOIN tblClientAddress ON ActiveCustomers.ClientNumber=tblClientAddress.ClientNumber
WHERE IntakeCount > ExitCount AND tblClientAddress.CurrentResidence = True; 下面是Query2的代码：
SELECT a.[Level], max(a.dte) AS nextDate, IIf([Type1Date]<[Type2Date],"t1","t2") AS [Type]
FROM (
select [Level], Type1Date as dte
FROM CommunicationTable WHERE ClientNumber=1
UNION
select [Level], Type2Date as dte
FROM CommunicationTable WHERE ClientNumber = 1
) AS a
GROUP BY a.[Level], IIf(a.[Type1Date]<a.[Type2Date],"t1","t2"); 有没有人可以教我如何设置它才能正常运行？
回答 1
Stack Overflow用户
发布于 2013-10-05 20:02:58
当您有像这样的连接和嵌套查询时，它有助于描绘向外部查询“公开”的内容。Query2有一个嵌套查询a，它不公开ClientNumber，因此Query2不能(也不会尝试)向连接公开ClientNumber。在Query2上试试这个
SELECT a.[Level], max(a.dte) AS nextDate, IIf([Type1Date]<[Type2Date],"t1","t2") AS [Type], a.ClientNumber
FROM (
select [Level], Type1Date as dte, ClientNumber
FROM CommunicationTable WHERE ClientNumber=1
UNION
select [Level], Type2Date as dte
FROM CommunicationTable WHERE ClientNumber = 1
) AS a
GROUP BY a.[Level], IIf(a.[Type1Date]<a.[Type2Date],"t1","t2");这样，外部查询就可以在连接的两端“看到”ClientNumber列。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19190986
复制相关文章
相似问题
腾讯云开发者
