SQL Statement that returns dups

  • Thread starter Thread starter Treebeard
  • Start date Start date
T

Treebeard

Hi,

I need an SQL query that returns the dups of a specific field.

For example, if I have a table with two fields, PIndex and BOOKID and it
contained these records:

PINDEX BOOKID
1 AAAAA
2 BBBBB
3 CCCCC
4 AAAAA
5 DDDDD
6 AAAAA
7 EEEEE
8 CCCCC
9 FFFFFF
10 GGGGG

I would like the query to return this:

1 AAAAA
3 CCCCC


Thanks,

Jack
 
1. Create a query into the table.

2. Depress the Totals button on the toolbar (Upper sigma icon.)
Access adds a Total row to the grid.

3. Drag BookID into the grid.
Accept Group By in the Total row under this field.

4. Drag PIndex into the grid.
Choose Count in the Total row.
In the Criteria row, enter:
1
Click to expand...

5. Drag PIndex into the grid again
Choose Min in the Total row.

The query groups by BookID (step 3), returns only records where the count is
greater than 1 (step 4), and displays the lowest PIndex value (step 5).
 
Hi,


Make a query like:

SELECT max(pindex) As mpindex, BookId
FROM myTable
GROUP BY BookId


save it as, say, Q1.


Make a second query:

SELECT *
FROM myTable As a
WHERE NOT EXISTS (SELECT *
FROM myTable As b INNER JOIN q1
ON b.pindex=q1.mpindex
WHERE a.index=b.index )



Sure, you can change the NOT EXISTS construction by something you may feel
better, but the actual query should allow you do delete those records, if
wanted (that query should be updateable).


If you don't need an updateable query, as example, you can try, in just one
query:


SELECT a.pindex, a.bookid
FROM myTable As a INNER JOIN myTable As b
ON a.bookid = b.bookid
GROUP BY a.pindex, a.bookid
HAVING a.pindex <> MAX(b.pindex)



Hoping it may help,
Vanderghast, Access MVP
 
Allen & Michel

Thank you for your suggestions. Taking your hints, I finally came up with
the following which works real well.

Thanks Again,

Jack

SELECT First([PIndex ]) As [PindexF], BOOKID
FROM tblOrderDetail
GROUP BY BOOKID
HAVING Count(*)>1;
 
Back
Top