Adding +1 every year.

J

Joshy

I have a database that has records of children in a club. This record has got
a YearLevel field so i know what year they are in at the school. Now i would
like to only have to add there year once and that every time a year goes past
it automaticly updates itself. Eg. If one child was in year 10, the next year
the form would say he is in year 11.

Now the next part is that if they are in year 12, and it goes to year 13 i
want all those records to be moved to an Exsisting Members table. So they are
not deleted but are moved to a new table for future references.

Thanks in advanced
Josh
 
W

Wayne Morgan

First, if you have the date they started, you could calculate the year when
you needed it rather than updating it. However, to update it, you would run
an Update query, adding 1 to the field.

Example:
UPDATE TableName SET TableName.FieldName = TableName.FieldName + 1;

You would then run an Append query followed by a Delete query to copy the
records with 13 to the new location and delete them from the current
location.

Example:
INSERT INTO [Existing Members] SELECT TableName.* FROM TableName;

If the field names don't match, you would specify the field names for each
table in the order that they should line up. Next, you would delete all
records in the original table that also exist in the [Existing Members]
table.

Example:
DELETE TableName.* FROM TableName WHERE TableName.IDField In (SELECT IDField
FROM [Existing Members];);

Try this on a copy of the database first to make sure it does what you want.
 
A

Arvin Meyer

Your Year Field is actually the result of a calculation, and as such does
not need to be stored. It can be recalculated in a query, form, or report
instantly whenever required.

If the data in the members table is the same, or almost the same as the
other data, you should only have 1 table. Use a field, or a calculation to
determine which level of membership there is. Access/Jet is not a server
based engine and therefore does not have Triggers so you'd manually (ie at
least in response to an event like a command button or semi-automatically
like the open event of a form) have to move the record if you did maintain
multiple tables.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
J

Joshy

Thanks for you quick replys guys. I'll be trying that soon, though wayne,
where would i be putting that code?

Thanks again
Josh
 
W

Wayne Morgan

The "code" is the SQL view of queries. You would need to modify any names in
it to match the names in your database. To run the queries from code, you
would use syntax similar to the example below. As far as where to put the
code, it depends on how and when you want it run. You could put it in the
Open event of your initial form so that it will run every time the database
is open. You could also place it in the Click event of a button to run it
manually whenever desired.

Example:
Dim db As DAO.Database
Set db = CurrentDb
db.QueryDefs("Query1").Execute dbFailOnError
db.QueryDefs("Query2").Execute dbFailOnError
db.QueryDefs("Query3").Execute dbFailOnError
Set db = Nothing

If you prefer, you could use the SQL directly in the code instead of saving
it as queries. If so, you would change the above to:

Dim db As DAO.Database, strSQL As String
Set db = CurrentDb
strSQL = "UPDATE TableName SET TableName.FieldName = TableName.FieldName +
1;"
db.Execute strSQL, dbFailOnError
'repeat above 2 lines for each of the remaining 2 queries
Set db = Nothing
 
Top