Select X.* From tablexyz X
Join (SELECT Field1 FROM tablexyz GROUP BY Field1 HAVING COUNT(*) > 1) as Y
On X.ID=Y.ID

Or something like it.



-----Original Message-----
From: Mark Williams [mailto:mwilliams22@y...]
Sent: Wednesday, January 16, 2002 3:01 PM
To: sql language
Subject: [sql_language] Selecting duplicate records with SQL in VB


Hi 
I am trying to simply retrieve from a single table,
all records that have duplicate data in one specified field.

I have tried using Count and Group and this works fine if I only select 
one field to display for the result
ie 
SELECT Field1 FROM tablexyz GROUP BY Field1 HAVING COUNT(*) > 1

I would basically like to do the following,however I do not have the 
correct syntax and would be extremely grateful if someone could point me 
in the right direction.

SELECT * FROM tablexyz GROUP BY Field1 HAVING COUNT(*) > 1

ie select all records that have duplicate data in field1, perhaps GROUP is 
not the way, however I cant quite get the right format to use.

Many Thanks
Mark
$subst('Email.Unsub').


This electronic message transmission, including any attachments, contains information from PacifiCare Health Systems Inc. which may
be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not
the intended recipient, be aware that any disclosure, copying, distribution or use of the contents of this information is
prohibited.  

If you have received this electronic transmission in error, please notify the sender immediately by a "reply to sender only" message
and destroy all electronic and hard copies of the communication, including attachments.