Duplicate records problem

[ZOR]

i have a major problem with duplicated records in the system. Before I eradicate them, I am trying to get proof its not in the database design, but user error. The main table is called TXMASTERS, in which there is a Barcode field. I have checked there are no duplicates in this table. Related to this table is one called TXCLIPS. In this table there is a field called Start which has an eight digit time field. Also in the table there is a comments field. I have found duplicate entries of the start time, and of the comments field in the same record, however I have also found duplicate start times, but slightly different worded text in the comments field, as though the record had been added at a seperate occasion.

I am trying to make 2 seperate queries, one to show duplicate child records having the same text in the comments field, and another of duplicate records having different text in the comments field.

TXMASTERS Table
ID1 (Primary)
Barcode

TXCLIPS Table
ID1 (Foreign)
ID2 (Primary)
Start
Comments

I have tried the wizards duplicates query but got nowhere.

Many thanks

 

[ZOR]

What I think I need is how to do a query that shows three fields 1.Barcode number, 2.start 3. Comments for records where there is a duplication of the start time field value of the same Barcode number. Thanks

 

[PHV]

What about this ?
SELECT M.Barcode, C.Start, C.Comments
FROM (TXMASTERS M
INNER JOIN TXCLIPS C ON M.ID1 = C.ID1)
INNER JOIN (
SELECT ID1, Start FROM TXCLIPS GROUP BY ID1, Start HAVING Count(*)>1
) D ON M.ID1 = D.ID1

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

 

[ZOR]

Thanks PHV. It looked like it was working, but on looking at some records having multiple duplicated Barcode/Start and then doing a query on the suspected Barcode/ID2/Start, records were found not to have duplication. Any ideas, thanks

 

[ZOR]

Something must be accumalating in the query. If I do a straight query on the start field there are 9,846 records. If I run the query above, the query shows 39,554 records? Thanks

 

[ZOR]

I managed to get a query together that works.

SELECT A.Start, A.Barcode, A.ID2, A.Comments
FROM A
WHERE (((A.Start) In (SELECT [Start] FROM [A] As Tmp GROUP BY [Start] HAVING Count(*)>1 )))
ORDER BY A.Start, A.Barcode;

Many thanks

 

[ZOR]

Spoke too soon. Yes it shows duplicated starts, but does not match them being within the same barcode number. Any ideas please?

 

[ZOR]

I must have done something good today, managed to get there with this.

SELECT A.Start, A.Barcode, A.ID2, A.Comments INTO Duplications
FROM A
WHERE (((A.Start) In (SELECT [Start] FROM [A] As Tmp GROUP BY [Start],[Barcode] HAVING Count(*)>1 And [Barcode] = [A].[Barcode])))
ORDER BY A.Start, A.Barcode