Stipulate criteria for Delete query

My Access knowledge is very limited, but I have set up a one table database (with a dual key of NAME & DATE) to use as a storage facility for Excel. I'd like to be able to do a Delete Query for all records for any horse that had its last race more than 3 years ago (Say 1100 days). So in the following example the 3 records for both Suavito & Scandiva would be expunged based on the date in red.

From research it seems I have to do a Select Query then change it to a Delete Query, but I've got no idea how to phrase the criteria to achieve the result I want.

Any help appreciated.

NAME DATE
Zevada 13/07/2014
Zevada 25/04/2013
Zevada 10/01/2011

Suavito 10/09/2013
Suavito 1/09/2013
Suavito 15/07/2013

Stipulate 8/03/2015
Stipulate 9/11/2014
Stipulate 13/10/2012

Scandiva 7/09/2013
Scandiva 18/02/2013
Scandiva 17/10/2012

Hi swallis,

You need to build a query that will only show "last race more than 1100 days ago". Read about the DateDiff function at:
https://www.techonthenet.com/access/...e/datediff.php

Then create a query using the DateDiff for one of your columns in the query builder. After it is selecting those old races, change the query to a Delete Query and run it. All those old records will then be deleted.

Thanks Marvin. If I've read that right, it will help, but I can't see how that selects the most recent date for each horse?

As Marvin has suggested, here is the SQL statement needed to delete records greater than 1100 days old.

PHP Code: 

DELETE Table1.Name1, Table1.Date1, DateDiff("d",[Date1],Date()) AS Diff
FROM Table1
WHERE (((DateDiff("d",[Date1],Date()))>1100)); 


BTW: The words Name and Date are reserved words in Access and should never be used as a field name. You can see that in my SQL statement I have changed the field names. Here is a link to reserved Access terms.

http://allenbrowne.com/AppIssueBadWord.html

Thanks for the response, Alan. Your solution is the same as Marvin's - it removes all older records for all horses. If you look at the table in my original post, what I'm trying to achieve is deletion of all 3 starts for Suavito and Scandiva, but keep all 3 starts for the other two, as they have had starts Less than 1100 days ago.

I looked at your link to reserved names and it's pretty scary for a dabbler. Suppose I'll have to change the name of my table - FORM

I don't understand your request. You have stated twice that you want to delete any records older than 1100 days. Both Marvin's and my solution do just that. Any thing older than 1100 days is deleted. Are you only interested in particular horses with records older than 1100 days? If that is is the case then you will need to stipulate in your query to only delete particular horses.

Yes. I'm looking to winnow out all retired/dead horses. Get rid of every record for them, but keep every record for any horse that is still likely to race, including their records which are more than 1100 days ago. The query needs to look at the most recent date raced for each horse and if it's more than 1100 days ago, delete all records for that horse. If the most recent date raced is less than 1100 days ago, I want to keep all records for that horse - including any which are more than 1100 days ago. That is what I don't know how to do and am asking help for.

Cannot post SQL statement directly. Here is a sample db with the layouts for determining which horses have not raced in 1100 days.

Feeling frustrated. I've downloaded 2 different Accdb viewers and still can't see your database - and I did unzip. Could you possibly upload it as an Mdb file please Alan?

ok. Will do.

Thanks Alan. Copied your Sql across and ran both queries and it selects the right horses to delete, but only lists the names and last date. How do I use that to delete all fields and all records for each horse?

You will need to run a delete query against the original table.

PHP Code: 

DELETE Table1.HorseName
FROM Table1
WHERE (((Table1.HorseName) In (Select [HorseName] from [Query2]))); 


Fantastic! Thanks for all your help Alan. Works perfectly.

Glad to have been of help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks. It is customary to give feedback to those that have helped you by clicking on the asterisk (Add Reputation) at the bottom of their post.

Did the rep. Forgot the Solved. Now done.