Search code examples
vbams-accesspivot-table

Can I create a query on people attending workshops with a many to many relationship showing a matrix of how many workshops in common?

I'm using MS Access 2016 I have a visitor table, a workshop table and a linking attending table. I would like to query this and get a matrix of numbers showing each one in relation to each other in a two way table. The number I would like is how many workshops they have in common.

example of what I'm trying to achieve
example of what I'm trying to achieve

I have done this with vba and some horrible report structuring... now user wants to filter and change things. I'm hoping I can possibly do this as a query. I'm a sql newbie so am hoping someone can point me in the right direction here. Is this possible using a crosstab query? I can't seem to find an example of what I need.

My attempt so far is sadly lacking.

TRANSFORM Count(tblAttending.WorkshopID) AS CountOfWorkshopID SELECT tblAttending.VisitorID, tblVisitors2.Name FROM tblAttending INNER JOIN tblVisitors2 ON tblAttending.VisitorID = tblVisitors2.VisitorID GROUP BY tblAttending.VisitorID, tblVisitors2.Name PIVOT tblAttending.VisitorID;

In the end, I've settled on this...

TRANSFORM nz(Sum(visitingpairs.CommonWorkshop))+0 AS SumOfCommonWorkshop SELECT visitingpairs.VisitorName1 FROM (SELECT V1.VisitorID AS VisitorID1, V1.Name AS VisitorName1, V2.VisitorID AS VisitorID2, V2.Name AS VisitorName2, Nz(Count(A.WorkshopID),0) AS CommonWorkshop FROM (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.WorkshopID GROUP BY V1.VisitorID, V1.Name, V2.VisitorID, V2.Name ) AS visitingpairs GROUP BY visitingpairs.VisitorName1 PIVOT visitingpairs.VisitorName2;

Solution

  • Would this work for you:

    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

    It is hard to test because you didn't give test data matching desired output.