Assistyance with SQL requested 2

[VickyC]

hello

I was able to get great help from the report forum a few days ago, and I'd like to try my luck again with a related problem.

In the table below, GroupID runs from 1 to 200. For each GroupID, there are exactly 4 values of Position, running from 1 to 4.

[tt]
GroupID Position V1 V2
1 1 67 79
1 2 115 77
1 3 81 2
1 4 56 29
2 1 11 53
2 2 91 8
2 3 13 109
2 4 88 13
3 1 97 67
3 2 44 115
3 3 17 81
3 4 22 56
..... ... .... ....
200 4 56 75

[/tt]
Notice that the 4 values of V1 having GroupID = 1 are identical (in value and in order) to the 4 values of V2 in Group3. So, I need SQL that outputs the GroupID values corresponding to these matches. In this case, the output would be...

[tt] G1 G2
1 3[/tt]

If there were, say, 12 such matches, then the query output would have 12 records.

Thanks in advance for any assistance.
Vicky C.

 

[dhookom]

I would expect you could create crosstabs with "P" & Position as the column headings. One crosstab would use V1 as the value and the other V2 as the value. Then join the two crosstabs on the P1, P2, P3, and P4 fields.


Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Same idea as Duane but without external queries:

SELECT G1,G2
FROM (
SELECT A.GroupID AS G1,MAX(A.V1) AS P1,MAX(B.V1) AS P2,MAX(C.V1) AS P3,MAX(D.V1) AS P4
FROM ((tblGroup A
INNER JOIN tblGroup B ON A.GroupID=B.GroupID)
INNER JOIN tblGroup C ON A.GroupID=C.GroupID)
INNER JOIN tblGroup D ON A.GroupID=D.GroupID
WHERE A.Position=1 AND B.Position=2 AND C.Position=3 AND D.Position=4
GROUP BY A.GroupID
) A INNER JOIN (
SELECT A.GroupID AS G2,MAX(A.V2) AS P1,MAX(B.V2) AS P2,MAX(C.V2) AS P3,MAX(D.V2) AS P4
FROM ((tblGroup A
INNER JOIN tblGroup B ON A.GroupID=B.GroupID)
INNER JOIN tblGroup C ON A.GroupID=C.GroupID)
INNER JOIN tblGroup D ON A.GroupID=D.GroupID
WHERE A.Position=1 AND B.Position=2 AND C.Position=3 AND D.Position=4
GROUP BY A.GroupID
) B ON A.P1=B.P1 AND A.P2=B.P2 AND A.P3=B.P3 AND A.P4=B.P4

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Thanks to both Duane and PHV. I saw Duane's solution proposal first, and found it to produce perfect results (though I assumed you meant creating crosstabs on "P" and GroupID, not "P" and Position).

PHV's solution is what I had originally been attempting, but I couldn't get the syntax of the JOINs to work out. Thanks for clearing this up.

Vicky C.

 

[JonFer]

I think this will also work and can be created with the query designer:

SELECT A.GroupID AS G1_V1, B.GroupID as G2_V2
FROM tblGroup A INNER JOIN tblGroup B
On A.Position=B.Position And A.V1=B.V2
Group By A.GroupID, B.GroupId
Having Count(*) = 4