How to select Duplicate Records on 50 fields

[pdupreez]

I have a table with 51 fields, and I could have duplicate records where the values of all 50 fields might be the same. I need to exclude those records from my select query, but cannot use two copies of the same table joined on each field (Query too complex error) and distinctrow does not work (single table) and a primary index can only be 10 fields. I tried to add a dummy table to the query and join the two on a field, and then use a "distinctrow" selection from the non-dummy table, but it only works on the one-side of the join. How do I do this, as I need to have distinct records for the statistical analysis I do on the data (STDEV, AVE etc)?

Regards

Pieter

 

[Malchik]

Hi,
For example, lets say you have a table call TEST with two fields (ID:Autonumber[Key] and Name:Text). You have these records in it
1 A
2 B
3 C
4 A
5 C

create this kinf of query:
SELECT DISTINCTROW ID, Name
FROM Test
WHERE Name In (SELECT [Name] FROM [Test] GROUP BY [Name] HAVING Count(*)>1 )
ORDER BY Name

You should end up with:
4 A
1 A
5 C
3 C
Mal'chik