Query that deletes multiples but keeps the newest

[jlclark4]

Is it possible to create a query that would delete and duplicates on a certain field but keep the newest ones based on another field?



Fields:
AutoID (AutoNumber)
Number (Text)
Capture_Source (Text)
r_Creation_Date (Time Stamp mm/dd/yyyy hh:mm:ss AM/PM)
Doc_Type (Text)
Creation_Date (Date/Time Date Only)

I would want to delete any duplicate "Number" field but keep the newest "r_Creation_Date" one

See table DailyClaimForLoss in attachment. Id want to keep the 222222 with the date 1/15/2012 7:16:00 PM.

[orange]

Yes it's possible. But a few questions/comments

Why do you have 2 creation date fields?
Number is a poor choice for a field name since it is a reserved word in Access.
Why not CertNo or similar?

I'd recommend a lookup table for the Capture Values
EDI, FAXMAIL, Print, ClaimStation, PAPER, UPLOAD
and also for DocumentType Claim for Loss, Supplemental Claim

I don't know your business process for deleting a record(s), but will you need these records for audit or history in future?
If so, you could have an associated Yes/No field and set it to indicate "this recorded is logically deleted"

[jlclark4]

The information in that table is imported from a export that is done daily. Data in the table in access can be minuplated anyway that is needed. There is two creation date fields to avoid massive coding to sort by date only in the future. The field in the real environment is not called number.

I have no decent knowledge of the different types of queries (append, delete, etc) and how to create them. I've only worked with Union Queries (other then basic queries to pull certain information).

I would need this broken down for me to understand how to get it done. I want to have everything run on a macro in the morning (automatically). Currently I have a macro set up to import the list (which adds to the table) and to delete blanks (because sometimes there are blanks on the spreadsheet I am inporting in)