Deleting Duplications

[dz2k7]

Hi Guys,

We get data daily from outside database that has duplications.
Sometimes I have the same line 3 or 4 times, but all the data is identical in all fields.

I did the query that shows me those dups.

SELECT Allinv.Fake
FROM Allinv
WHERE Allinv.Fake In (SELECT T1.Fake FROM Allinv As T1 GROUP BY T1.Fake HAVING Count(*)>1)
ORDER BY Allinv.Fake;

Fake is the Key field and it should be unique but it is not so far.

I tried to make delete query like this

DELETE Allinv.*
FROM Allinv
WHERE Allinv.Fake In (SELECT T1.Fake FROM Allinv As T1 GROUP BY T1.Fake HAVING Count(*)>1)
;

But it deletes all lines having duplications.

How i can make just one line to stay but all dups killed?

I probably could make a table with unique dups, then delete all dups and append the unique table, but it needs to be done in 3 queries, which is not that nice as it could be.

Thanks for your constant support.

 

[Brianwarnock]

I think that you could just create an append query that is an aggregate query having Groupby on all fields.

Brian

 

[dz2k7]

Neeee.... this table has about 2 mil lines and about 200 columns.
That will work forever and Access will not be able to handle 2 tables like that at the same time just because of 2GB limit.
And I have no chance to get SQL Server (predicting possible advices)

 

[Brianwarnock]

You get 2million lines daily?
Wow that's some data.

Brian

 

[dz2k7]

O yee! I drive some inventory in Canada

But can somebody help?