Mike's Questions

[Mike375]

This thread has been split as it diverts from the original question asked elsewhere.

I find myself in general ageement with Pat Hartman, which I guess is the law of averages.

It really depends of what the data is for. For example, in the case of age I store that both as a calculation and as a fixed in a table figure. Why?? Firstly I want your age stored when you purchased the insurance policy and I also want your age now. Now of course someone could say that I can calculate your age at the start of the policy. However, there can be errors in the info from the insurance company or the person might have backdated the policy. Also it would be something else I have to make. Actually, I store age as a calculation on the "client" files and as a fixed value on the policy benefits. So if we sell you a policy with 4 different benefits (and hence 4 rows) a Setvalue sticks you current "calculated" age on each row.

Mike

 

[Rich]

Paul,

Actually, I store age as a calculation on the "client" files and as a fixed value on the policy benefits. So if we sell you a policy with 4 different benefits (and hence 4 rows) a Setvalue sticks you current "calculated" age on each row.

Mike

Not only storing calculated data but multiple duplication, Jesus what a mess

 

[Mike375]

Not only storing calculated data but multiple duplication, Jesus what a mess

Actually it goes one step further because the client's name, Home and Bus Phone and date of birth are also stored on each policy benefit row. Also on each row of policy benefits are my phone numbers, fax and email address. Every single row of policy benefits has them.

My phone details etc are also stored on every client record.

Mike

 

[Rich]

Actually it goes one step further because the client's name, Home and Bus Phone and date of birth are also stored on each policy benefit row. Also on each row of policy benefits are my phone numbers, fax and email address. Every single row of policy benefits has them.

My phone details etc are also stored on every client record.

Mike

Are you trying to impress us?
Your post is a classic example of why storing calculated values is not a good idea, if the dob is entered wrongly in the first place you have to go to every record and update it, instead of just one!

 

[Mike375]

Are you trying to impress us?
Your post is a classic example of why storing calculated values is not a good idea, if the dob is entered wrongly in the first place you have to go to every record and update it, instead of just one!

Not so.

I have a few macros for different situations that open all the records related to the client (and the same with other One to Many) and then do a Setvalue and down the records they go. The multi record form is closed and the macro moves to the next client record and repeats the process.

Those macros are in fact part of a process for us to bring in the updated policy details the insurer sends. One of the problems with their data is that they have a "policy number" as the client and as such they can have the same person stored several times and with each policy number being linked to the policy benefits.

When there is a problem and I am looking at the policy benefit and talking to the person in the insurance company (who is trying to figure out why Bill Smith is on their records 3 times) .....I say to the person...look, I will fax you something. I click on a text field in the row which opens a form and prints it...with all my details and all the policy holders details and of course the policy details. That way when someone else in the insurance company gets the fax they know who it is from and who it is for.

Yes, I do know that a query could link the two tables etc. Dealing with the life insurance company data is not easy and that is the reason that we are among the very few who have their data in our own data base, as distinct to a stand alone thing you get from them. We have a computer group in Australia who make a thing called Pro Planner whicj they bot sell to life insurance agents and in some cases the agents get it free via an insurance company. However, they are only able to bring clients in, not the policy benefits associtaed with those clients.

Mike

 

[raskew]

Does anyone know the author of:

"Never argue with idiots, they bring you down to their level, then beat you with experience."

Never has it seemed more appropriate, unless perhaps with last week's painful saga.

Bob

 

[Mike375]

Bob,

Check Pat Hartmans answer to a question I asked last month.

http://www.access-programmers.co.uk/forums/showthread.php?t=66987

In principle that is the problem the computer group have bringing in the insurance policy details from the insurance company, that is, the data does not match up for the way they would want to do things.

But since I am not meeting first normal with appt slots going across a page, can you tell me away to achieve the following without resorting to what I do.

Seven days of appt times must show on the screen with 10 appt slots per day showing the appt time and estimated finishing time and of course the ability to scroll though a future 12 months.

Date ____ ____ ____ ____ ____ ____ ____ ____ ____ ____

........____ ____ ____ ____ ____ ____ ____ ____ ____ ____


Date ____ ____ ____ ____ ____ ____ ____ ____ ____ ____

........____ ____ ____ ____ ____ ____ ____ ____ ____ ____


You click on the day and a drop down time list opens and when you click on the time the times sort in ascending order across the page.

The actual appointment details are stored in another table that is one record per appt. The above is so you can see what is on for appts, although when you enter the time it forms part of the diary entry as a new record is opened in the appt table and all details SetValued to the new record including the writing/notes that are entered in an unbound text box at the top of the page.

I am sure you would agree that the following would be lest than useful for a telemarketer trying to juggle time slots with someone he is trying to sell an appt to

5.30pm 8.00am 10.30am 4.00pm 7.00am 2.30pm 1.30pm 9.30am

So the appts times must be sorted A to Z across the row.

So again, I have to have at least 7 days showing (from "today") when the form opens and 10 slots per day showing.

Mike

 

[Brianwarnock]

Paul,

I find myself in general ageement with Pat Hartman, which I guess is the law of averages.

Mike

I find myself in total agreement with Pat, I guess that's the law of experience.

I think Mike is just trying to wind us up now, I thought that he realised that his application could be done better by an experienced IT professional but quite rightly was taking the pragmatic point of view that
a It was no longer being developed
b was going to remain on the same software platform
c had worked for 7-8 years and needed to last only 2-3 more
d would cost too much to redevelop for no gain

but it now appears that he believes it is the way to go!!

On a lighter note Pat sounds like the kind of girl all us guys are looking for a 21 year old blue-eyed blond with over 30 years experience.

Brian

 

[Mike375]

Brian,

From post:

but it now appears that he believes it is the way to go!!

Well, it some ways it is. A case in point is the diary system. A diary system on the computer is very difficult for telemarketing IF the telemarketing is done over the longer term and for the same people and doubly so if the telemarketer is doing it for a group of people over the longer term.

The "solution on paper" was quite simple...the appt slots across the page sorted in ascending order and at least 7 days showing on form opening. I made mine a few years ago and before I did I asked a few of the developers in the insurance company about the "sort" across the row. In each case I got basically the same answer as Pat gave.

So is it better to have a table that allows the diary system to deliver the goods but does not meet first normal or is better to have a table meet first normal but not deliver the goods.

Having said that, the general assessment in your post is pretty close to spot on

Mike

 

[Rich]

So is it better to have a table that allows the diary system Mike

what do your replies have to do with the original question on storing calculated data

 

[Mike375]

what do your replies have to do with the original question on storing calculated data

About the same as those I responded to, including your posting

Mike

 

[Mike375]

Thanks for the answers, especially Pat - my problem is that a customer wants to see management information - on the start up of a large application - things like top ten cutomers over different services. Anyway - the time taken to do the calculation is prohibitory - certainly to put on everyones startup screen.

I am wondering what else I can do rather than use calculated fields - this kind of info is available in the database via reports and forms, but they want it on startup -admittedly the calculations can probably be done more efficiently, but I doubt they could be done in a time delay most users would be happy with.

Others must have had this problem. What was the solution?

Paul,

I don't know if this will bear any relationship to your problem but here goes

I need to see all the details (as a summary) for 20 categories of prospect that are being called. This includes such things as number of names remaining in the data base for each category, calling time goals and a summary of the previous day calling results. There is no way to pull it all together in one go.

To do each category is fine, no problems at all and each one will run nice and fast.

What I do is have a form based on a table and there are 20 records, one for each category. I also have 20 forms for each category that display the calculated results, that is , they are "live". I run a macro that opens Category 1 Form and then opens the single form with 20 records for the matching category number and then the macro does a bunch of SetValue actions and then goes on to repeat the process through the 20 categories.

It would appear you have a situation that is not totally different in that you have 10 customers over 10 services.

Mike

 

[Mile-O]

It would appear you have a situation that is not totally different in that you have 10 customers over 10 services.

Where did he say that?

 

[Mike375]

Where did he say that?

things like top ten cutomers over different services.

I should only have had one 10

over different services An unknown at this stage.

Mike

 

[Mile-O]

Paul, what sort of data do you have? What's your calculation(s)? How is it currently done?

Details, details, details...

 

[Rich]

Paul,

I don't know if this will bear any relationship to your problem but here goes

I need to see all the details (as a summary) for 20 categories of prospect that are being called. This includes such things as number of names remaining in the data base for each category, calling time goals and a summary of the previous day calling results. There is no way to pull it all together in one go.

To do each category is fine, no problems at all and each one will run nice and fast.

What I do is have a form based on a table and there are 20 records, one for each category. I also have 20 forms for each category that display the calculated results, that is , they are "live". I run a macro that opens Category 1 Form and then opens the single form with 20 records for the matching category number and then the macro does a bunch of SetValue actions and then goes on to repeat the process through the 20 categories.

It would appear you have a situation that is not totally different in that you have 10 customers over 10 services.

Mike

Children: don't even attempt to emulate this madness

 

[Mike375]

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

 

[Rich]

There's no point in anyone replying to your post, as you keep telling us you've not the slightest intention of changing anything, nor do you wish to learn. Why not employ somebody?

 

[KenHigg]

i n s a n e

 

[Mike375]

There's no point in anyone replying to your post, as you keep telling us you've not the slightest intention of changing anything, nor do you wish to learn. Why not employ somebody?

Because that is one part of the data base that I would like to improve and in addition it more or less sits to one side. In other words, a bit like working on the roof of the house as opposed to changing only one brick in the foundations.

Why not employ somebody? Because it is not vital as the other system will do all that is required and I suspect the cost would be very high. I don't know, maybe there is a simple answer. Like when I asked about sorting across a row and as Pat Hartman said, I would be on my own with something like that.

Mike