Delete duplicate data in Access

[Charles]

Hi MS Access & MS SQL Server Gurus !

I am trying to delete duplicate data from a LINKED SQL Server 2000
table (LinkedTable) and insert the result into a local (in Access)
table.

The following code works in SQL Server 2000 but NOT in Access, could
any one tell me what's wrong with it?

SELECT a.id, a.change_id AS chgid, a.Action_desc as Actiondescr INTO
LocalTable
From dbo.LinkedTable as a
Where a.id = (
Select TOP 1 b.id
From dbo.LinkedTable as b
Where a.change_id = b.change_id
Order By b.time_stamp)

Thanks in advance !

[Sherwood Wang]

Re: Delete duplicate data in Access


"Charles" <dengcharles@ho tmail.com> wrote in message
news:bede28a1.0 408032049.6033e 1cb@posting.goo gle.com...[color=blue]
> Hi MS Access & MS SQL Server Gurus !
>
> I am trying to delete duplicate data from a LINKED SQL Server 2000
> table (LinkedTable) and insert the result into a local (in Access)
> table.
>
> The following code works in SQL Server 2000 but NOT in Access, could
> any one tell me what's wrong with it?
>
> SELECT a.id, a.change_id AS chgid, a.Action_desc as Actiondescr INTO
> LocalTable
> From dbo.LinkedTable as a
> Where a.id = (
> Select TOP 1 b.id
> From dbo.LinkedTable as b
> Where a.change_id = b.change_id
> Order By b.time_stamp)
>
> Thanks in advance ![/color]



no code please is stupid. you make 1 query and do job easy.

1 query is access SELECT find all record you want deleted. run query on sql
table. is OK? find all record you want deleted? OK now you turn SELECT to
APPEND you get data OK into local table yes? now you turn APPEND to
DELETE. you get sql record deleted. all simple 1 query please.

*Sherwood Wang MVP*