question about comparing rows in a table

[teach314]

I have a table with about 1000 rows.

ID  x0  x1  x2  x3  ... x9.
1   34  25  12   8      92
2   55  14   7  32      11
etc...

I want to identify any values of ID that have IDENTICAL values (x0 to x9) in the same order.
I did this by creating a query that uses 2 copies of the table and joins all 'x' fields from
each table. So far, so good!

For my own learning of SQL, I want to do the same thing using a NORMALIZED version of the table, like below:

ID   Col   x
 1    0    34
 1    1    25
 1    2    12
 1    3     8
....
 2    0    55
 2    1    14
etc...

This should be easy, I'm sure, but I can't get it work.
How can I identify the IDs that have the same x0 to x9 values in the same order?

thank you in advance

 

[billmeye]

You could set up a function to perform the concatenating and reference that in a query.

Function Concat(ID As String) As String
Dim Rst As DAO.Recordset, MySQL As String
MySQL = "SELECT * FROM tbl WHERE tbl.ID=" & ID & ";"
Set Rst = CurrentDb.OpenRecordset(MySQL, dbOpenDynaset)
Rst.MoveLast
Rst.MoveFirst
Do While Not Rst.EOF
Concat = Concat & Rst!X
Rst.MoveNext
Loop
Rst.Close
Set Rst = Nothing
End Function

SELECT ID, concat([ID]) AS AllVal
FROM tbl
Group By [ID]
HAVING (((concat([ID])) In (SELECT [AllVal] FROM [qryThisQuery] As Tmp GROUP BY [AllVal] HAVING Count(*)>1 )))
ORDER BY concat([ID]);

 

[BigRed1212]

I thought we did this already?

http://tek-tips.com/viewthread.cfm?qid=1712686