我正在使用 MS Access 2016,我有一个访客表、一个研讨会表和一个链接参加表。我想查询这个表并得到一个数字矩阵,显示每个数字之间的关系......
我使用的是 MS Access 2016,我有一张访客表、一张研讨会表和一张链接出席表。我想查询这些表,并得到一个数字矩阵,以双向表格的形式显示每个表之间的关系。我想要的数字是它们有多少个共同的研讨会。
我想要实现的目标的例子
我已经使用 vba 和一些糟糕的报告结构完成了此操作...现在用户想要过滤和更改内容。我希望可以将其作为查询来执行。我是 SQL 新手,所以希望有人可以在这里为我指明正确的方向。使用交叉表查询是否可行?我似乎找不到我需要的示例。
我迄今为止的尝试却很遗憾地失败了。
转换 Count(tblAttending.WorkshopID) 作为 CountOfWorkshopIDSELECT tblAttending.VisitorID,tblVisitors2.NameFROM tblAttending INNER JOIN tblVisitors2 ON tblAttending.VisitorID = tblVisitors2.VisitorIDGROUP BY tblAttending.VisitorID,tblVisitors2.NamePIVOT tblAttending.VisitorID;
最终,我决定这么做...
TRANSFORM nz(Sum(visitingpairs.CommonWorkshop))+0 AS SumOfCommonWorkshopSELECT visitingpairs.VisitorName1FROM (SELECT V1.VisitorID AS VisitorID1, V1.Name AS VisitorName1, V2.VisitorID AS VisitorID2, V2.Name AS VisitorName2, Nz(Count(A.WorkshopID),0) AS CommonWorkshopFROM (tblAttending AS A INNER JOIN tblVisitors AS V1 ON A.VisitorID = V1.VisitorID) INNER JOIN (tblAttending AS B INNER JOIN tblVisitors AS V2 ON B.VisitorID = V2.VisitorID) ON A.WorkshopID = B.WorkshopIDGROUP BY V1.VisitorID, V1.Name, V2.VisitorID, V2.Name) AS visitingpairsGROUP BY访问对。访客姓名1PIVOT 访问对。访客姓名2;
这对你有用吗:
transform sum(t.cnt)
select PersonRow
from (
select count(iif(a.WorkshopID<>b.WorkshopID, 1, null)) as Cnt, a.LastName as PersonRow, b.LastName as PersonColumn
from (
select av.VisitorID, av.LAstName, aa.WorkshopID
FROM tblVisitor av
inner join
tblAttending aa
on av.VisitorId=aa.VisitorID
) a
inner join
(
select av.VisitorID, av.LAstName, aa.WorkshopID
FROM tblVisitor av
inner join
tblAttending aa
on av.VisitorId=aa.VisitorID
) b
on a.VisitorId<>b.VisitorId
group by a.LastName, b.LastName )t
group by PersonRow pivot PersonColumn
测试很困难,因为您没有提供与所需输出相匹配的测试数据。