Mile-O-Phile said:
Paul, what sort of data do you have? What's your calculation(s)? How is it currently done?
Details, details, details...
Hey Mile,
I have a problem here that relates to stored Vs calculated and it has been with me for about 4 years. If you have a fix, I will send you a bottle of something. This is something I would really like to fix.
Basically the cold calling through to sales goals is on one side of the equation and the various results and numbers associated with the actual calls and sales are on the other sisde of the equation. These goals include the amount of calling time for each category of different days, the expected number of contacts and appointments made etc and a start time and finish time for the goal period. There is also the facility to pull up the goals and results between any two specified dates between the start and finish date.
I have two ways of doing it but the system I would prefer won't do it all for me.
The system I have that will do it all puts a new record in a table for each new day. So as the clock clicks over to Midnight Monday the details will be placed in a new record for the calling time etc for that category on that day. The other system I have is on a "calculated basis" and for 20 categories a table has 140 records, that is, the Monday to Sunday by 20. The calling times etc are entered for the various categories and different days and also the varying expected "calling/sales" ratios. The calculations click over each day and the results are perfect (except for one problem)
To give you a picture of the avove is doing let's say we entered a goal starting date for this Thursday, 24 June. Whe you opened the form or query) you would see for the category
24/6/04
25/6/04
27/6/04
28/6/04
29/6/04
30/6/04
1/7/04
The monitor than relates each of those dates to Date() and then is able to calculate the various calling times, contacts etc that are expected from the goal commencing period to Date(). It also does to the date for the goal finishing period. To extract figures for a period during the goal there is a separate table and query that is a duplicate and the goal starting and goal finishing calculations (fields) are fed the two dates for the desired time.
Now to my problem with this system Vs the other system that ohysically creates a new record for each category and for each day.
With the other system we can change the calling goals to 0 when someone will be away on holdidays etc. Then when they retrun we just re enter the calling goals from 0 back to what they were. Since a new record is created each day then the 0 period is taken into account. Likewise, we can increase or decrease the calling goals for a period of time and again because a new record is created each day this will be taken into account.
But I am completely unable to even begin to think of how the changing of the goal settings during a goal period with the calculated system can be done.
I could make up something where if the person was going to be away for week the system could calculate the goals for that period and then subtract that from the other calculation but that woold be way out on day one.
By the way, the plus of the calculated system is the updating system is simply caused by the Date() change. The other system runs for quite a while to update and especially when there is 3 days of updating. Also, if we want to certain projections the calculated system just needs the date on the computer changed. Apart from chaning the daily goals during the goal period another plus the "new record each day" offers is that it is simpler and quicker to extract goals and results fior a selected period because they are already there.
Mike