I have a database that I import data to each month. At the moment I manually update a table each month that identifies which months (in my case they are called SPs, this is a 4 week period instead of an actual month), I then use this table where true in queries to report on my data. I would like to automate this process of identifing the last 3 SPs instead of having to do it manually each month.
The file name is SP06 (2013) Centre Database
I have 2 columns that are updated. One of the columns is called “MostRecentSP” this is updated with the below code, which works perfectly.
Sub SelectCurrentSP()
strFullSPName = Trim(Left(CurrentProject.Name, 11))
strSQL = "UPDATE tblSPList SET tblSPList.MostRecentSP = True " _
& "WHERE (([tblSPList]![DisplaySP]='" & strFullSPName & "'));"
DoCmd.RunSQL strSQL
End Sub
What I would like to do is update the “Last3SPs” column as well. I also have a column in this table with a running number ID. I was thinking there must be a way to update the "Last3SPs" when updating the “MostRecentSP”, and count back 3 SPs with these marked as true. I'm just not able to work out the code myself.
I hope this makes sense? Really hard to put in writing what I’m trying to do.
Attached is a screen shot of my table.
Any suggestions would be greatly appreciated.
Thanks