Selecting Unique Records

A

Always Learning

Hi Guys,

Could you help me please?

I have a table with three columns.
1. RefNum
2. Salutation
3.Email Address

I want to pull out of the single table all the records with a unique email
address. so if there are 4 records with the same email I want just 1 of the
4 records with that email address and the others to be deleted.
If there is only 1 occurance of an email address pull that out also.


Thanks for your help, I appreciate it.

Best Regards,

Steve.
 
B

Bill Edwards

The following will pull a single record for each distinct email address. In
the case of duplicates it will keep the record with the highest value in
RefNum:

SELECT tblTest.Email, tblTest.RefNum, tblTest.Salutation
FROM tblTest
WHERE
tblTest.RefNum IN (SELECT max( refnum) from tblTest as b GROUP BY email)
 
A

Always Learning

Hi Bill,

That looks perfect, I will try it later.

Thanks for your time, I appreciate it.

Best Regards,

Steve Wilson.
 
A

Always Learning

Hi Bill,

Tried that and it works a treat, thankyou.

Sorry to put on you again but is there a way to put the records it finds as
duplicates into a seperate table.

Thanks for your help.

Best Regards,

Steve Wilson.
 
B

Bill Edwards

Create new table based on the duplicate records:

SELECT [tblTest]., [tblTest].[RefNum], [tblTest].[Salutation] INTO
tblDuplicateValues
FROM tblTest
WHERE (((tblTest.RefNum) Not In (SELECT max( refnum) from tblTest as b GROUP
BY email)));

Create new table based on just single records:

SELECT [tblTest].[Email], [tblTest].[RefNum], [tblTest].[Salutation] INTO
tblUniqueValues
FROM tblTest
WHERE (((tblTest.RefNum) IN (SELECT max( refnum) from tblTest as b GROUP BY
email)));
 
A

Always Learning

Hi Bill,

Thanks again. That worked perfect.
You certainly know your stuff.

Best Regards,

Steve Wilson.

Bill Edwards said:
Create new table based on the duplicate records:

SELECT [tblTest]., [tblTest].[RefNum], [tblTest].[Salutation] INTO
tblDuplicateValues
FROM tblTest
WHERE (((tblTest.RefNum) Not In (SELECT max( refnum) from tblTest as b GROUP
BY email)));

Create new table based on just single records:

SELECT [tblTest].[Email], [tblTest].[RefNum], [tblTest].[Salutation] INTO
tblUniqueValues
FROM tblTest
WHERE (((tblTest.RefNum) IN (SELECT max( refnum) from tblTest as b GROUP BY
email)));


[QUOTE="Always Learning"]
Hi Bill,

Tried that and it works a treat, thankyou.

Sorry to put on you again but is there a way to put the records it finds
as
duplicates into a seperate table.

Thanks for your help.

Best Regards,

Steve Wilson.

address.
In
[/QUOTE]
[/QUOTE]
Click to expand...
 
Top