Reset counter ID to 1 for every new year in MS Acc

问题:

I have two tables , tblContacts and tblTrackJob. Every time a new record is about to be added in tblContacts , id like to assign its JobNo field to the value of whatever ID is in tblTrackJob has stored. Id like the value to reset to 1 every year.

For example...

 first name, last name, JobNo, dateEntered
 bob         smith       01    1/1/2013
 john        doe         02    1/2/2013
 mary        Thomas      03    1/3/2013

 Joe         Henry       01    1/1/2014 

回答1:

Since we're dealing with Access 2010 we can create a Before Change data macro for the [tblContacts] table to assign the [JobNo] value:

The macro will find the largest [JobNo] in the table from records with a corresponding Year([dateEntered]), and then add 1 to that value to give us the [JobNo] for the new record. If no existing records are found for the corresponding year then the new record gets a [JobNo] equal to 1.