S
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???
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???