deleting from a 'find duplicates query'

[Guest]

Hi I'm trying to delete duplicates from a table I have combined from 2
others, but I get an error message "could not delete from specified tables"
can any one help? I 'm new at this and can't find the right answer

I'm using this code but I'm sure it's not right

DELETE *
FROM [SELECT First(plant.[Plant number]) AS [Plant number Field],
Count(plant.[Plant number]) AS NumberOfDups
FROM plant
GROUP BY plant.[Plant number]
HAVING (((Count(plant.[Plant number]))>1))]. AS [%$##@_Alias];
many thanks Dic

 

[Guest]

Hi Dic,

Access won't allow you to delete records from a Grouped by query. Try
deleting records manually after you use the wizard to create a find dups
query OR (if you have numerous duplicated records) write a make table query
where you Group BY the field(s) the represent the tables uniqueness and take
the First record of the rest of the fields.

You can then write another find dups query on your new table to check if the
duplicated records were deleted.

hope this helps.

 

[Van T. Dinh]

See if the following Microsoft Knowledge Base article helps:

http://support.microsoft.com/kb/209183

 

[Guest]

Many thanks that has done the trick thanks for your help
regards
Dic

See if the following Microsoft Knowledge Base article helps:

http://support.microsoft.com/kb/209183

--
HTH
Van T. Dinh
MVP (Access)

Hi I'm trying to delete duplicates from a table I have combined from 2
others, but I get an error message "could not delete from specified
tables"
can any one help? I 'm new at this and can't find the right answer

I'm using this code but I'm sure it's not right

DELETE *
FROM [SELECT First(plant.[Plant number]) AS [Plant number Field],
Count(plant.[Plant number]) AS NumberOfDups
FROM plant
GROUP BY plant.[Plant number]
HAVING (((Count(plant.[Plant number]))>1))]. AS [%$##@_Alias];
many thanks Dic