Calculation

[gbaby_2604]

I am currently with a database that has 3 calculated fields in it. The fields are called:
1. Total Calls Per Week
2. Total Calls Per Month
3. Total Calls

Total Calls per Week is suppose to calculate the number of calls that we get for one week. I would like this field to be cleared after the current week is over. Ex. If we have gotten 50 calls I would like that number to move to Total Calls Per Month and then the Total Calls per Week field will be cleared so we can start all over again.

Total Calls Per Month which is then suppose to calculate the number of calls we get for that month. After the month is over I would like this field to be cleared. Ex. Since we have gotten 50 calls for the first week then it is suppose to hold 50 calls and then say that we have 500 calls total for that month, it is suppose to move that 500 to Total Calls field and then Total Calls per Month will be cleared so we can start all over again for the next month.

Total Calls is suppose to hold the number of calls that we have gotten all together. This field does not need to be cleared out.

Please email me if you have any help that I could use.

 

[Idjit]

How are you logging calls? I assume you have a table containing a record for each call, and that one field in that table is the date of the call?

If this is the case, then the answer to your question is you shouldn't be storing calculated fields in your database. Set up a query that gives you a count of the number of calls received during the current week and month as well as the total, using the date of the calls in your selection criteria. Then every time you run the query you'll always have up-to-date information.

Here's an example of an expression you would use to select records for only those calls received in the current month:
Year([CallDate]) = Year(Now()) And Month([CallDate]) = Month(Now())

HTH