Access Delete Duplicate Records In a Table

there are many ways but the easiest one is alreay available from access itself

Is AID the primary key for the table? (autonumber field)

After finding duplicates I need to delete based on my criteria mentioned above

yes AID is primary

shieldsco

Please post a representative sample of record values (AID, [Appeal Number], [Appeal Assign Date]) so that I can test.

It would be ideal if you posted both before and after versions.

1. A screen shot is not data that I can import into a table for testing.
2. I don't see any duplicate Appeal Assigned Date values in what you posted.

It's the appeal number that is duplicated. I want to delete duplicate appeal numbers where the assign appeal date is the oldest date. Keeping in mind that there may be more than two duplicate appeal numbers.

Appeal Number      AID      Appeal Assigned Date
3-1859962137      9897099      6/17/2019
3-1859962137      9894747      5/17/2019
3-2424386907R1      9911774      8/27/2019
3-2424386907R1      9891767      5/2/2019
3-2427005706      9911819      8/28/2019
3-2427005706      9894604      5/17/2019
3-2530649894      9911814      8/28/2019
3-2530649894      9894601      5/17/2019
3-2634469212      9897100      6/17/2019
3-2634469212      9894780      5/17/2019
3-2653086011      9887209      12/13/2018
3-2653086011      9887190      12/11/2018
3-2687964973      9911815      8/28/2019
3-2687964973      9894602      5/17/2019
3-2698387632      9911816      8/28/2019
3-2698387632      9894603      5/17/2019
3-2698589834      9914546      9/27/2019
3-2698589834      9900578      7/15/2019
3-2712510576      9911812      8/28/2019
3-2712510576      9894599      5/17/2019
3-2722950060      9914547      9/27/2019
3-2722950060      9900579      7/15/2019
3-2726925824      9887212      12/13/2018
3-2726925824      9887203      12/12/2018
3-2726925824      9887193      12/11/2018

See Excel table attaxhed
Book1.xlsx

you can try this self join query to select those that you want to delete

select distinct dupes.* 
from tblagedcasegoal dupes, tblagedcasegoal full
where dupes.[Appeal Number]=  full.[Appeal Number]
and dupes.[Appeal Assign Date]<full.[Appeal Assign Date]

Select allOpen in new window

and this once you are sure those are the correct ones

delete dupes.*
FROM tblagedcasegoal AS [dupes], tblagedcasegoal AS [full]


WHERE dupes.[Appeal Number]=full.[Appeal Number] AND dupes.[Appeal Assign Date]<full.[Appeal Assign Date];

Select allOpen in new window

So, you want to delete the following rows?

3-1859962137	9897099	6/17/2019
3-2424386907R1	9911774	8/27/2019
3-2427005706	9911819	8/28/2019
3-2530649894	9911814	8/28/2019
3-2634469212	9897100	6/17/2019
3-2653086011	9887209	12/13/2018
3-2687964973	9911815	8/28/2019
3-2698387632	9911816	8/28/2019
3-2698589834	9914546	9/27/2019
3-2712510576	9911812	8/28/2019
3-2722950060	9914547	9/27/2019
3-2726925824	9887212	12/13/2018

Select allOpen in new window

I want to keep those rows. keep the latest date. Delete the oldest dates

No records returned

select distinct dupes.*
from tblagedcasegoal dupes, tblagedcasegoal full
where dupes.[Appeal Number]=  full.[Appeal Number]
and dupes.[Appeal Assign Date]<full.[Appeal Assign Date]

did you try my suggestion?

yes no records returned

I just run your data with my query this is the results
those are the ones you want deleted

yes

If you are cleaning up your database it makes sense to eliminate duplicates.  If you are looking for a quick fix to get the most current listing then Excel can be a simple workaround.  

Download the data
Make a table
Duplicate the table (so you are keeping the original intact)
Sort the date column descending on the duplicate table
Then sort the Appeal Number in Ascending Order
Select the whole table
Go to Data > Remove Duplicates
When window comes up, select the Appeal Number column only and press okay

See attached example
EE12.xlsx

Please test this

DELETE [Q_29173178].*
FROM [Q_29173178]
Where [Q_29173178].[Appeal Assigned Date] <> 
(SELECT Max([Appeal Assigned Date])
FROM [Q_29173178] As MAD
Where [Q_29173178].[Appeal Number] = MAD.[Appeal Number]
GROUP BY [Appeal Number]
HAVING (Count([Appeal Assigned Date]))<>1)

Select allOpen in new window

If my query isn't performant, we can go the two query route, where we populate a new table (Make Table query) with the max dates and then join that new table to the original table (inner join).

here is your query already tested

delete * from tblagedcasegoal as d1
where d1.[Appeal Assign Date]<>
(
select max(dupes.[Appeal Assign Date] )
from tblagedcasegoal dupes
where dupes.[Appeal Number]=  d1.[Appeal Number]
)

Select allOpen in new window

I get prompted for  parameter values

you are typing something wrong or missing something
in access create new query in QUERY DESIGN MODE
Close the show table dialog without selecting anything,
click the SQL VIEW button so it allows you to paste the code

and paste the code I gave you
click RUn and thats it

That's exactly what I did

I attached the db so you can see yourself, maybe because of some setting the identifiers, parenthesis or something has to be different in your setup
Database1.accdb

Same issue - I get prompted for parameter values

I tried on two different computers one running Access 365 and the other Access 2016 . The same result .. I get prompted for parameters

have you tested my query?

yes - I get prompted for parameters

Did you replace the Q_29173178 strings with the name of your table?

yes

Are the column names EXACTLY the same as the ones you posted with the sample data?

Im baffled, I even sent you my working DB with query included and also you get asked for parameters, My guess is either an internationalization setting or something messed up in your install

I tried on two different installs and got the same result.. prompted for paramaters

Are the column names EXACTLY the same as the ones you posted with the sample data? - YES

Capture.JPG
Capture1.JPG

Please check for missing [ and ] surrounding table or column names.

I attached your DB with the full tblAgedCaseGoal
Database1--1-.accdb

Are you testing the queries against with the 99 table or the big table?

the big table

Using temp tables will speed this up - to run in a few seconds.

In the attached database, run these queries to create the final table holding the Ids to delete:

1. CreateAgedCaseMax
2. CreateAgedCaseId
3. CreateAgedCaseIdDelete

Finally, run the delete query:

• DelteAgedCase

DELETE *
FROM tblAgedCaseGoal
WHERE tblAgedCaseGoal.ID In (Select Id From [tmpAgedCaseIdDelete]);

Select allOpen in new window

Database2.accdb

Thanks to all especially aikimark