Delete Query

[Guest]

Let's try this.
I have a registration data base for a youth sport program (thanks to a lot
of useful advice from the people in here). A youth can be enrolled in the
program from the age 5 until the age 14, so nine years is the most. The way
it is set up for a day to day operation, ever year they do registration, on
the 1st of August they import all the information based on the Season into
Excel for manipulating. (More people can use excel than can use queries to
get the information they need to use). So they store all the Seasons
information in an Excel book.

What they need now, is there a way to run a delete query or command that
will delete all records 10 years old based on the season. In the form and in
the table for the Players is a field Seasons (combo box) and it is a foreign
key in the Players table and a primary key in the table Seasons.

Thanks
CoachBarkerOJPW

 

[John Spencer]

Unclear to me what your table structure is and what you want to do.

You have a Players Table and a Seasons Table. What does the Seasons table
have in it?

Do you have ONE record for each player in the Players table or do you have
one record for each player and each season in the players table?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Players table has all the information regarding a player, Seasons table is a
Table basically for filling the combo box for Seasons. There is one record
for each player in the Players table, that includes the season. Every year a
player registers they update that players information. After 10 years, they
are too old to play so they would like to delete all records of players that
because of their age are ineligible to play. Each season they export all
records into a Excel workbook and that is how they store all their old
records.

Does that help?

 

[John Spencer]

Not much. Do you have a date of birth field in the players table?
If so, you should be able to use that to identify which records to
eliminate.

Let's ask the question this way. If you have one player record in front of
you, how would you know that the player is too old to play this season?

If it is by date of birth then you could delete records from the database
based on the date of birth or you could simply not export those records to
the spreadsheet.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Yes there is a DOB birth field in the table, and by looking at one record
there is a calculated field that shows the players playing age (Aug 1 of
current year - DOB) for that current season. And yes that value is stored in
the table (please no grief about storing calculated fields) so I guess I
could create a delete query that is based upon the players age where the
value is greater than 14?

 

[John Spencer]

I would probably use the DOB instead of the Age. Since if you are storing
the calculated age, it is going to be as of the last time the record was
updated and that could be 12 or 11 or ??? if the person did not re-register
after that date. You could either calculate and enter the cut-off date 14
years ago or use the following expression to identify the persons.

< DateSerial(Year(Date())-14,8,1)
That calculates the Aug 1st of the year that is 14 years prior to the
current year (determined by your system clock)

Field: DOB
Criteria: <#08/01/1993#

OR use

Field: DOB
Criteria: < DateSerial(Year(Date())-14,8,1)

You may adjust the cutoff date as needed since I was not quite sure if the
max age was 13 or 14.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..