Run an action according to date

[ShannonMarie]

I have a db almost done and would like to add an archive table. At the end of the year I need to archive a total field and an ID field. How can I set it up that on December 31 @ approx 11:59 the record values are sent to the "Archive" table? I have the logic of what I need to do, but I'm unsure of how to pull and then clear those values in the main table. Any ideas are gratefully appreciated. Thanks.

 

[bjackson]

why not add a archive field (yes/no) to your table,then instead of sending the data to a new table,just update the yes/no field

 

[ShannonMarie]

I need it date sensitive. The database is at a fire department. All employee responses are cummulitive. The chief recognizes the top 3 responders at the end of the year. So I need to total the number of fire calls and the number of rescue calls at the end of the year and store them. He would like to have a "response" history on each employee. A check box would suffice for an IF test. He wants all employee activity stored though. I am unsure of how to implement to query or code according to the end of the year.

 

[bjackson]

without knowing what values your tables hold and relationships etc its a bit hard to help

here is a simple sql that would return the top 3 values of a table
i am assuming you have some sort of employee id and station id
etc.The sql would negate the need to store calculated values,
it may help you,it may not

SELECT TOP 3 Count(CallOuts.CallOutId) AS CountOfCallOutId, CallOuts.StationId, CallOuts.EmployeeId, Format$([CallOuts].[CallOutDate],'yyyy') AS [CallOutDate By Year]
FROM CallOuts
GROUP BY CallOuts.StationId, CallOuts.EmployeeId, Format$([CallOuts].[CallOutDate],'yyyy'), Year([CallOuts].[CallOutDate])
HAVING (((Year([CallOuts].[CallOutDate]))=DatePart("yyyy",[Yourdate])))
ORDER BY Count(CallOuts.CallOutId) DESC;