remove duplicate rows based on one column

[stephenson22]

I would like to remove rows based on duplicate recoreds in one column. see
table below.

KEY DETAIL1 DETAIL2
X1 A F
X2 B G
X1 C H
X1 D I
X3 E J

I would like to end up with the record set of:

KEY DETAIL1 DETAIL2
X1 A F
X2 B G
X3 E J

Basically eliminating the rows with duplicate record "X1"

I have investigated certain bits of code on the forum, but nothing seems to
give me the desired resultset I require.

I have tried to use the following code:

SELECT
tb_duplicates.refval, tb_duplicates.detail1, tb_duplicates.detail2

FROM
tb_duplicates

WHERE
(
(
( tb_duplicates.refval) In
(
SELECT [refval]
FROM [tb_duplicates] As Tmp
GROUP BY [refval],[detail1], [detail2]
HAVING Count(refval)>1
)
)
)

But this only gives me the resultset below:

KEY DETAIL1 DETAIL2
X1 A F
X1 C H
X1 D I

The above is not what I want - can anyone help???

 

[Wayne-I-M]

Hi

Check out this link

http://support.microsoft.com/default.aspx?scid=KB;EN-US;209183

Hope this helps

 

[stephenson22]

Thank you very much.

That was so EASY - its unbelievable.

You have me shouting down my office for joy.

You saved me alot of time and money.

Kind regards.
--
Learning SQL and Access

Hi

Check out this link

http://support.microsoft.com/default.aspx?scid=KB;EN-US;209183

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do

I would like to remove rows based on duplicate recoreds in one column. see
table below.

KEY DETAIL1 DETAIL2
X1 A F
X2 B G
X1 C H
X1 D I
X3 E J

I would like to end up with the record set of:

KEY DETAIL1 DETAIL2
X1 A F
X2 B G
X3 E J

Basically eliminating the rows with duplicate record "X1"

I have investigated certain bits of code on the forum, but nothing seems to
give me the desired resultset I require.

I have tried to use the following code:

SELECT
tb_duplicates.refval, tb_duplicates.detail1, tb_duplicates.detail2

FROM
tb_duplicates

WHERE
(
(
( tb_duplicates.refval) In
(
SELECT [refval]
FROM [tb_duplicates] As Tmp
GROUP BY [refval],[detail1], [detail2]
HAVING Count(refval)>1
)
)
)

But this only gives me the resultset below:

KEY DETAIL1 DETAIL2
X1 A F
X1 C H
X1 D I

The above is not what I want - can anyone help???