Queries to select and delete duplicate values

Hi all,

I have a dataset with a number of duplicate values.  I want to select records where the values in fields NAME, Year, Type, Levels, Values are exactly the same.  Once this is verified I need to delete the duplicates keeping one record.

I've tried the find duplicates query wizard and I don't think I have the correct result.  See below code block, is it correct?

SELECT BaseTable.[NAMES] AS [NAMES Field], BaseTable.[Year] AS [Year Field], BaseTable.[Type] AS [Type Field], BaseTable.[Levels] AS [Levels Field], BaseTable.[Values] AS [Values Field], BaseTable.[NAMES] AS NumberOfDups
FROM BaseTable
WHERE (((BaseTable.[NAMES])>1) And ((BaseTable.Values)>1));

Open in new window


Thanks
AndyC1000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
is there a unique id field in your table?
0
AndyC1000Author Commented:
No unique id field.
0
Rey Obrero (Capricorn1)Commented:
add an autonumber field and call it ID,

then run this query

select * from BaseTable
where BaseTable.ID Not In(select Min(b.ID), b.[NAME], b.[Year], b.[Type], b.[Levels] from Basetable as b  group by  b.[NAME], b.[Year], b.[Type], b.[Levels])

you will get all the duplicated records except for the one that have the minimal id on the duplicated records.


to delete this records, just use this delete query

delete * from BaseTable
where BaseTable.ID Not In(select Min(b.ID), b.[NAME], b.[Year], b.[Type], b.[Levels] from Basetable as b  group by  b.[NAME], b.[Year], b.[Type], b.[Levels])


. as a pre-caution create a backup copy of the table before running the delete query.
0
AndyC1000Author Commented:
Thanks for your reply.

I have the following error 'you have written a sub query that can return more than one field without using exists reserved word in the main queries FROM clause. Revise the select statement of the subquery to request only one field'.

I'm not sure why this is appearing I used your query and I do need all the fields of a record where a duplicate exists.
0
SharathData EngineerCommented:
correction in capricorn1's solution.

select * from BaseTable
where BaseTable.ID Not In(select Min(b.ID) from Basetable as b  group by  b.[NAME], b.[Year], b.[Type], b.[Levels])

Open in new window


To delete those records
delete * from BaseTable
where BaseTable.ID Not In(select Min(b.ID) from Basetable as b  group by  b.[NAME], b.[Year], b.[Type], b.[Levels])

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.