increase a number by 1 in a field on the same day each year automatically

[jetanotherone]

I have a relational database for a school that has the students (scholastic year) 0-12. I would like to increase this field by 1 each year on the 20 January eg. 1 will become 2 the following year 3 then 4......



It sounds simple but I have not been able to do this.
Can it be done in the table where I have a field called [scholastic] or in a query that will update [scholastic] automatically each year.

Many thanks

[June7]

Generating custom identifier is a fairly common topic. Here is one https://www.accessforums.net/showthread.php?t=23329

[jetanotherone]

Thank you for your reply but I was hoping for something more simple, like increasing the number by +1 on the same date each year (dd/mm) and of cause retain that new number. It is to represent the number of years the student has attended school.


my brain is telling me it should be as easy as saying..... on the 20 January add 1 to the existing number but I'm not clued up on expressions and syntax. I know there is a thing called DateValue but I have trouble making that work. I'm I on the wrong track. Up till now I have changed this manually but I would like to make it automatic if possible.

[June7]

I misunderstood what you wanted. This is not an identifier.

You can run an UPDATE query. However, there are risks to this. What if the UPDATE is accidentally run more than once?

Ideally, the number of years a student has attended would be calculated based on enrolment or attendance records.

[isladogs]

As June said, use an update query.
One way of preventing this being run more than once is to do this as part of a wider routine updating the academic year.

For example, lets suppose your current academic year is called 2017.

On 20 January 2018, the routine does the following things in order:
1. Checks the academic year in your database. If its 2017 go to step 2 - otherwise exit
2. Add 1 to scholastic year using an update query
3. Do any other changes needed
4. Adds 1 to the academic year => 2018

[jetanotherone]

That all sounds good but being new at this I need help with putting this into an expression/formula and will this coding need to go into the field part of the query or the criteria

[June7]

Simplest approach is to build an Access query object and then run that query by selecting it from the Navigation pane. Use the query designer to build the query. Basic Access functionality. The SQL statement would look like:

UPDATE tablename SET SCHOLASTIC = SCHOLASTIC + 1;

If you want to automate this then that will require code, VBA would be my preference.

The real trick is figuring out what event to put code into. Could be a button Click or could be automated to run when the database opens. Code would only run if conditions met. As ridders52 suggests, have a table that identifies the Academic year (whether this is a single record that is updated or multiple records and new record is added is up to you, depends if there is info about the year you want to document). If it does not agree with the current year on Jan 20 then run the code. The more 'user friendly', the more code. Something like:

If Month(Date()) = 1 And Day(Date()) = 20 Then
    If DMax("AcadYr", "tablename") <> Year(Date()) Then
          CurrentDb.Execute "UPDATE Students SET SCHOLASTIC = SCHOLASTIC + 1;"
          CurrentDb.Execute "INSERT INTO tablename(AcadYr) VALUES(" & Year(Date) & ")"
    End If
End If

Advise no spaces nor punctuation/special characters (underscore only exception) in naming convention. Also, all upper case is not recommended (harder to read).

[isladogs]

First you need to change your Scholastic field datatype from short text to number (integer).
Secondly check why you appear to have an empty record.

In the update query, update the scholastic field to [Scholastic]+1
That's why it needs to be a number

For the update query on the table containing the academic year field, put 2017 in the criteria and 2018 in the 'update to' item
There is a better solution involving the year part of the date

First get both update queries ready
For the conditional part of the code, you would use

If Year(Date)>AcademicYear Then

A procedure can then be written to include both update queries together with any other related changes

[Micron]

Why even store this calculation? That's so often discouraged, and I don't see the need for it here either.
Why not just express the number as the difference between the current year and the start year, formatted as an integer?
Something like Int(Year(Date())-Year(#01/21/2012#))

[isladogs]

As an ex teacher who write apps for schools, that was my first thought.
However, one reason for not doing that is that some students will join the school late so calculating the difference in years between admission date and the start of the new academic year will not always give the correct answer.

[Micron]

Gotcha. The terms are somewhat ambiguous to me now that I've re-read the entire thread. I guess Academic year is currently 2017, even as late as next June (in my locale) since the year starts in September. Now I'm thinking Scholastic year is a fancy way of saying the grade you are in (e.g. 11) yet that's likely out to lunch. So the idea of updating in January is weird since if I am in 11 in September, I'm still in 11 in May. I should just leave well enough alone now! June7's idea looks pretty straightforward. All you'd have to do is decide on how you want it to happen. If automatically, use an AutoExec macro to call a function to figure out if it's time, and either prompt or not. Or the opening of any form that is pretty much guaranteed to be opened can do the check.

[isladogs]

I'm UK based so our academic year starts in September & ends in July.
It is therefore 2017-18 at the moment.
However, the principle is the same

I assume Scholastic Year = Year Group
Of course, another issue is that students are not always placed in the correct year for their age group

For exactly this situation, I use the approach I outlined in post 5.
In my case, several other fields are updated at the same time as year group & academic year
For example, change tutor group from 7AZ to 8AZ

[June7]

Yes, the condition for the academic year may be a bit more complicated. Need more info. For instance, why Jan 20? Don't return from winter break until Jan 20?

[jetanotherone]

Thank you for your help. I will try it out.

I am from Australia so our school year starts at the end of January and ends December. The school that I'm doing this database for is a school for children with disability. We have students starting school for the first time at different ages.

I guess all I need is for a field of numbers (0-12) to increment by 1 at a set date of the year and then keep that number untill same time the following year when the process starts again. If this could be done automatically it will mean that no one will have to remember to push a button or activate anything should I not be around any more.

[June7]

Okay, that would be summer break for you, if academic year was Sep to Jun, but since it's not, moot point.

Might want to get familiar with resources at http://allenbrowne.com/tips.html, Allen is an Aussie as well.