Printable View
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]
What's the table name and the fields that make a unique key?
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. ;)
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;
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.
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]