Subtract a number from a total after 1 year

[GreenMtns]

I have to keep track of a point system for drivers. Each driver starts out with 7 points. If the driver has an accident a number of points will be subtracted based on a category. That number will not be added back to the total for 1 year. I have tried to find a way to go back 1 year and add the number back to the total but am not having any luck.

 

[PWise]

Where are you keeping track of accident dates and points deducted

 

[GreenMtns]

I have a table with the drivers personal information. It would be here that the point standing would be stored. The categories and their related points are kept in another table. Accident records are in another table, tblAccidents. The tblCategory is a look up table. When entering the information about a new accident, I would choose a category suitable for that event. I would like, at that point, the correct number of points to be subtracted from the total. When the tblDrivers is opened with a form I would like to have access check to see if any stored points are over 1 year old and if so subtract them.

 

[PWise]

Select DriverId,Sum(Points) As TotalPoints
From(
Select DriverId,StartingPoints As Points
From TblPersonalInformation
Union
Select DriverId,-tblCategory.PointsDeducted
From tblAccidents
inner join tblCategory
on tblCategory.CategoryId=tblAccidents.CategoryId
And datediff("d",tblAccidents.AccidentDate,Date())<365) As qPoints
Group by DriverId