viewing all records with duplicate field values

[shadowsong]

I need to a query to return all records where the serial number in the
DATA.SN field appears more than once in the table DATA.

Doing a count() on that field and then grouping only returns one
record with a duplicate value, not every record with with a duplicate.
I assume it's due to the grouping, but count() is the only way I know
to find dupes and as far as I know count() has to be used with GROUP
BY.

Any ideas? Here's what I have at the moment:

SELECT data.DLRNAME, data.DLRNUM, data.SALESNAME, data.CUSTNAME,
data.ADDR1, data.ADDR2, data.CITY, data.STATE, data.ZIP, data.PHONE,
data.EMAIL, data.SOLD, data.MODEL, data.UPC, data.SN, data.ENTERED,
Count([data].[SN]) AS Count
FROM data
group by data.DLRNAME, data.DLRNUM, data.SALESNAME, data.CUSTNAME,
data.ADDR1, data.ADDR2, data.CITY, data.STATE, data.ZIP, data.PHONE,
data.EMAIL, data.SOLD, data.MODEL, data.UPC, data.SN, data.ENTERED
having Count([data].[SN])>1;

 

[Guest]

Use two queries. First one to count the number of times a SN is used with
criteria >1.
Then in the second query use the first query joined on SN in the table.

 

[shadowsong]

Use two queries. First one to count the number of times a SN is used with
criteria >1.
Then in the second query use the first query joined on SN in the table.
--
KARL DEWEY
Build a little - Test a little

I need to a query to return all records where the serial number in the
DATA.SN field appears more than once in the table DATA.

Doing a count() on that field and then grouping only returns one
record with a duplicate value, not every record with with a duplicate.
I assume it's due to the grouping, but count() is the only way I know
to find dupes and as far as I know count() has to be used with GROUP
BY.

Any ideas? Here's what I have at the moment:

SELECT data.DLRNAME, data.DLRNUM, data.SALESNAME, data.CUSTNAME,
data.ADDR1, data.ADDR2, data.CITY, data.STATE, data.ZIP, data.PHONE,
data.EMAIL, data.SOLD, data.MODEL, data.UPC, data.SN, data.ENTERED,
Count([data].[SN]) AS Count
FROM data
group by data.DLRNAME, data.DLRNUM, data.SALESNAME, data.CUSTNAME,
data.ADDR1, data.ADDR2, data.CITY, data.STATE, data.ZIP, data.PHONE,
data.EMAIL, data.SOLD, data.MODEL, data.UPC, data.SN, data.ENTERED
having Count([data].[SN])>1;

That worked just fine until I went back in and looked at the query in
design view. Now the query table isn't editable.

Here are the queries:

[duplicates]
SELECT *
FROM data
RIGHT JOIN [count(sn)]
ON [count(sn)].sn=DATA.SN;

[count(sn)]
SELECT sn, count(sn)
FROM data
GROUP BY sn
HAVING count(sn)>1;

Why can I no longer edit or delete records within the query results?

 

[John Spencer]

You might try using the Duplicates query wizard. It would/should build
a query that looks like the following.

SELECT Data.SN, data.DLRNAME, data.DLRNUM, data.SALESNAME,
data.CUSTNAME,data.ADDR1, data.ADDR2, data.CITY, data.STATE, data.ZIP,
data.PHONE,data.EMAIL, data.SOLD, data.MODEL, data.UPC, data.SN,
data.ENTERED
FROM data
WHERE Data.SN in
(SELECT Data.SN FROM Data GROUP BY Data.SN Having Count(*) > 1)
ORDER BY Data.SN


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

Now the query table isn't editable.

Correct. No Totals query (your COUNT) nor any query including a totals query
is ever updateable!

You may need to use the DCount() function to find records with dups.

John W. Vinson [MVP]