Query to return full row where there is a Duplicate column

[ppitch]

Hi,

I am working on a program where I need to return every row that contains a duplicate serial number.

I have this working but it only returns the serial number and the count where as I need to return each row that the duplicate appears on.All the data is in one Table called Table1.

I have been informed I need to join this query back to the original table on the serial but cannot work out how to do it.

SELECT serial,


COUNT(serial) AS NumOccurrences
FROM address
GROUP BY serial
HAVING ( COUNT(serial) > 1 )


Any help would be greatly appreciated.

[TheShabz]

Save that query then throw it into another query. Join on the serial then get * from the address table.

Edit: I think I should elaborate a bit more. Save that query as qrySerialDupes. Then start a brand new query and put in the address table as well as qrySerialDupes. Treat the query like you would a table. Join on the serial and then select * from the address table.

[ppitch]

Thanks for your advise.

I have managed to get this working using 2 queries. I create a temporary table from the first then query this from a second query. It works but It's very manual at the moment.

I am looking at getting this working from one query so I can run from an external source.


First Query

SELECT [serial], count(*) INTO FirstQuery
FROM Table1
GROUP BY [serial]
HAVING count(*) > 1;


Second Query

SELECT [Table1].[ID], [Table1].[DATE], [Table1].[Customer], [Table1].[Address1], [Table1].[Address2], [Table1].[Address3], [Table1].[Address4], [Table1].[Postcode], [Table1].[Contact], [Table1].[Model], [Table1].[serial], [Table1].[Fault], [Table1].[Fault2], [Table1].[Fault3], [Table1].[Repair], [Table1].[repaircode], [Table1].[ponumber], [Table1].[Labourtime], [Table1].[Dateout], [Table1].[comments], [Table1].[datein], [Table1].[repairer], [Table1].[tracking], [Table1].[Loggedby], [Table1].[Quotedate]
FROM FirstQuery INNER JOIN Table1 ON [FirstQuery].[serial]=[Table1].[serial]
WHERE ((([Table1]![ID])>482));

[weekend00]

use Access's query wizard to create a "find duplicate query".

[TheShabz]

use Access's query wizard to create a "find duplicate query".

What, and make things easier?

But yea, you dont have to run two queries. As long as the first one remains a SELECT query, you can just literally put it into the design view window of a second query and treat is as if it was a table. When you run the second query, the first automatically gets run as well.

[ppitch]

Thanks again.

I never thought of that one. I will give it a go later tonight.