Update and delete query help

[1802]

Hi, I'm currently working with a database at college where I need to carry out a number of tasks. For some background info, I've got a member table where each user has an ID/Username type thing in the layout "1112SJ0001". 1112 = 2011/2012 - the year/period they joined. SJ = First letter of surname and first name, eg, John Smith. 0001 = sequential number. So 0001 will be the first ever member. At the beginning, any member with 1213.... (2012/2013) is considered a "Current" member in the members table under the field (MemberStatus). All others are left blank.

Some of the tasks I'm not sure on are listed below and any help will be appreciated:

1) Create a form with a button that, when clicked will:

Update all the members from 1 year ago so that their status says "Archived". Now, I've created an update query that takes the enrolment number and I have the formula - Left([Members]![ID],4) - with a critera of "1112". Therefore when the query is ran, all members who joined in 11/12 become archived.

This form also requires all members who joined in the period 10/11 and before to be deleted. So of course I made a delete query but this time with just the member status and a criteria of "Is Null".

To run the button on the form I created a macro that essentially opened the Archive query first (ran it), followed by the delete query so that any left overs that aren't archived or current members are deleted. This all works absolutely fine.


My question is (sorry if it's taken a while to get to this point, but I needed to make the scenario clear) although this query/form/macro works at the moment, surely when new members join, the formulas will become irrelavent. For example, in the period 2013/14, all members from the 2012/13 time will become the archived group and the 2011/12 will be deleted

So basically, what formula/criteria do I need to add to the archive query that will ensure this works every year without having to manually change the criteria from "1112" to "1213" and so on?

Many Thanks!

 

[jzwp22]

Welcome to the forum!

First, you should not be storing this: 1112SJ0001. This is essentially a calculated value and calculated values are generally not stored in a table but rather calculated on the fly when needed. Of course, the components that are used to make the value need to be stored. So I would have fields that store the beginning year of the period (i.e. 2011 in the case of 1112SJ0001) or more simply the date the person joined, the member's first and last names (in separate fields) and then a field for the sequential number.

If you only store the beginning year of the period or join date, the query criteria becomes simple (just comparing year values)

 

[JHB]

jzwp22 has right, but it doesn't help you now so criteria could be found so:

right(CStr(Year(Date) - 1), 2) & right(CStr(Year(Date)), 2)

Current year is Year(Date)