If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Delete Query
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 |
#2
|
|||
|
|||
Delete Query
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 .. "CoachBarkerOJPW" wrote in message ... 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 |
#3
|
|||
|
|||
Delete Query
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" wrote: 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 .. "CoachBarkerOJPW" wrote in message ... 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 |
#4
|
|||
|
|||
Delete Query
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 .. "CoachBarkerOJPW" wrote in message ... 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" wrote: 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 .. "CoachBarkerOJPW" wrote in message ... 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 |
#5
|
|||
|
|||
Delete Query
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" wrote: 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 .. "CoachBarkerOJPW" wrote in message ... 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" wrote: 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 .. "CoachBarkerOJPW" wrote in message ... 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 |
#6
|
|||
|
|||
Delete Query
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 .. "CoachBarkerOJPW" wrote in message news 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" wrote: 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 .. "CoachBarkerOJPW" wrote in message ... 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" wrote: 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 .. "CoachBarkerOJPW" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|