DELETE Duplicates Statement

[DDTiff]

Hello,

Can someone help me with the DELETE Duplicates Statement?

I have a table named Junction in MS Access DB which duplicates each record by 47 times. Is there a DELETE statement which allows me to reduce the records copy to just 1. Thank you for your help in advance.


DTiff

 

[forecasting]

Here is a link I found that should help.

http://www.4guysfromrolla.com/webtech/sqlguru/q051200-2.shtml

 

[harmmeijer]

You can use UNION.
select all the non id/autonumber fields like so:
SELECT somefield, someotherfield ...
then UNION that select statement with exactly the same select statement like so:
SELECT somefield, someotherfield FROM Junction
UNION
SELECT somefield, someotherfield FROM Junction


I have tried:
INSERT INTO myNODoubleTable
SELECT somefield, someotherfield FROM Junction
UNION
SELECT somefield, someotherfield FROM Junction


But this doesn't seem to work

 

[tlbroadbent]

Post your question in forum701 - Microsoft: Access Queries and JET SQL. This is a MS SQL Server forum. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[DDTiff]

Thank you all for helping out. I just got back to work, so I will try out the code, and let you know. Once again, I appreciate your concern and help.

DTiff