Wendell Menezes Wendell Menezes - 1 year ago 54
SQL Question

Trouble to build a SQL join

I want to check if the values of the 3 fields of the table "T2" appear together for each user in the table "T1", something joined like this:

![Tentative of illustration]1

Expected outcome:

enter image description here

Is this possible at all? If yes, can you please help to build a query for it?

Thank you

Link to the MS access file:

Answer Source

Assumptions based on posted example: Only 3 factors and factors can be in any field of T1.

Need a unique identifier field in each table, an autonumber should serve.

query: T1_UNION (A UNION query must be typed (or copy/paste) in SQLView of query builder.)
SELECT RecID_T1, "R1" AS Cat, R1 AS Data FROM T1 UNION SELECT RecID_T1, "R2", R2 FROM T1 UNION SELECT RecID_T1, "R3", R3 FROM T1;

query: T2_CROSSTAB
TRANSFORM First(T2.R) AS FirstOfR SELECT T2.U FROM T2 GROUP BY T2.U PIVOT DCount("*","T2","U='" & [U] & "' AND RecID_T2<" & [RecID_T2])+1;

query:
SELECT T2_CROSSTAB.U, DLookUp("Data","T1_UNION","Data='" & [1] & "'") AS IsMatch1, DLookUp("Data","T1_UNION","Data='" & [2] & "'") AS IsMatch2, DLookUp("Data","T1_UNION","Data='" & [3] & "'") AS IsMatch3 FROM T2_CROSSTAB WHERE ((Not (DLookUp("Data","T1_UNION","Data='" & [1] & "'")) Is Null) AND (Not (DLookUp("Data","T1_UNION","Data='" & [2] & "'")) Is Null) AND (Not (DLookUp("Data","T1_UNION","Data='" & [3] & "'")) Is Null));

Using Joe C's idea, CROSSTAB is not needed.

query: Data_GROUPED
SELECT T2.U, T2.R FROM T2 INNER JOIN T1_UNION ON T2.R = T1_UNION.Data GROUP BY T2.U, T2.R ORDER BY T2.U;

query:
SELECT Data_GROUPED.U, Count(Data_GROUPED.R) AS CountOfR FROM Data_GROUPED GROUP BY Data_GROUPED.U HAVING (((Count(Data_GROUPED.R))=3));

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download