Deleting Duplicate Records In An Access 2003 Table.

Attached is a Access 2003 PartForecast2015.xlstable where I have duplicate records for Fiscal Year 2015.  I want to remove all of the duplicate records for 2015.  Can someone please assist?  Thanks!
Steve EckermanSystems AdministratorAsked:
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.

Jeffrey CoachmanMIS LiasonCommented:
Duplicates in ALL fields?

Can you clearly identify, for us, a sample record and its duplicate?

This will save us from scanning each of your records there manually...

JeffCoachman
0
Steve EckermanSystems AdministratorAuthor Commented:
For Fiscal Year 2015 PSINO AIR107 has one record where all of the fields have a value and then there is another PSINO AIR107 record where the MGR field is null, then Annual Volume is null and all of the months are null.  So basically I need a SQL statement that will remove all Fiscal Year 2015 records where the PSINO is a duplicate and the MGR, Annual Volume and Months of the year fields are null.
0
Steve EckermanSystems AdministratorAuthor Commented:
Did that help Jeff?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
Ok, so not a "true" duplicate record...
0
Jeffrey CoachmanMIS LiasonCommented:
Then it looks like it would be easier to delete all record where the Manager, Annual Income, and all the months is/are empty...
0
Jeffrey CoachmanMIS LiasonCommented:
*Make a copy of the table in the database*
(or better yet, try this in a copy of the entire database)
Run this query:

DELETE PartForecast.Mgr, Len([Mgr]) AS Expr1, PartForecast.PSINO, PartForecast.AnnualVolume, PartForecast.Jan, PartForecast.Feb, PartForecast.Mar, PartForecast.Apr, PartForecast.May, PartForecast.Jun, PartForecast.Jul, PartForecast.Aug, PartForecast.Sep, PartForecast.Oct, PartForecast.Nov, PartForecast.Dec, PartForecast.Obsolete, PartForecast.PNSI, PartForecast.PRICE, PartForecast.Updatedate
FROM PartForecast
WHERE (((Len([Mgr]))=0) AND ((PartForecast.AnnualVolume) Is Null) AND ((PartForecast.Jan) Is Null) AND ((PartForecast.Feb) Is Null) AND ((PartForecast.Mar) Is Null) AND ((PartForecast.Apr) Is Null) AND ((PartForecast.May) Is Null) AND ((PartForecast.Jun) Is Null) AND ((PartForecast.Jul) Is Null) AND ((PartForecast.Aug) Is Null) AND ((PartForecast.Sep) Is Null) AND ((PartForecast.Oct) Is Null) AND ((PartForecast.Nov) Is Null) AND ((PartForecast.Dec) Is Null));

1749 Total Starting Records
  824 Records Deleted
  925 Records Remaining
0
Jeffrey CoachmanMIS LiasonCommented:
Triple check the results to see if all the records needing to be deleted, were deleted.
Also make sure no records remain that perhaps should have been deleted (due to missing any other criteria)
0
Dale FyeCommented:
Start with a SELECT Statement:

SELECT FiscalYear, Mgr, PSINO, AnnualVolume
FROM yourTable
WHERE FiscalYear = 2015
AND [PSINO] IN (
SELECT PSINO FROM yourTable
WHERE FiscalYear = 2015
GROUP BY PSINO
HAVING Count(*) >1)
ORDER BY PSINO, Mgr DESC

This should give you a list of all the duplicates, sorted by PSINO with the record containing a value in the MGR field on top.  If that is what you are looking for, you can change that query to a DELETE query.  Do this with a copy of your table first.

DELETE FROM yourTable
WHERE FiscalYear = 2015
AND [PSINO] IN (
SELECT PSINO FROM yourTable
WHERE FiscalYear = 2015
GROUP BY PSINO
HAVING Count(*) >1)
AND [MGR] IS NULL
AND [AnnualVolume] IS NULL
0
Jeffrey CoachmanMIS LiasonCommented:
Try Dale's solution first....
0
Steve EckermanSystems AdministratorAuthor Commented:
I made a backup before I ran the query that created the duplicates .  I will run your queries in the morning.  They both look good!  Thanks for the help to all!  I will let you know of the results.
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
Steve EckermanSystems AdministratorAuthor Commented:
They worked like a champ.  You guy's are true SQL gurus!!!  Thanks!!!
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.