advanced SQL queries - not for the faint-of-heart

[dwhawley]

earlier, i was trying to write a sql statement that would not copy duplicate values into a recordset. i found that you could do this using the DISTINCT keyword (fellow vb-world member, jlopez deserves some credit here). now i want to be able to identify those records that are duplicates and use them as part of an error log. but how do i capture them? are there any sql gurus out there? SQL for Dummies doesn't seem to be cutting it.

[Edited by dwhawley on 08-02-2000 at 04:36 PM]

[JHausmann]

What's the table name and the fields that make a unique key?

[dwhawley]

the table is tblEquipment
the fields are as follows:
ControlNo (this is assigned by the user)
Mfg (manufacturer)
ModelNo (model number)
SerialNo (serial number)

jhausmann, if you can get this one for me, the cyber-beers are on me. ;)

[BruceG]

If you are using Access, you can do something like this:

SELECT DISTINCTROW FIRST(ControlNo) AS FirstControlNo, COUNT(ControlNo) AS NumberOfDups
FROM tblEquipment
GROUP BY ControlNo HAVING COUNT(ControlNo) > 1;

[dwhawley]

bruceg! you win the prize! a cyber-beer on me! thanks a lot for you help. as you can tell from babblings, i have been working on this one for a while and i really needed a resolution. you wouldn't happen to have a good sql reference would you? nothing in my "SQL for Dummies" book would give me anything close to this.

[JHausmann]

That's what I get for taking a day off...no beer (virtual or otherwise) :)

[Edited by JHausmann on 08-04-2000 at 01:49 PM]