R
Rich
Guest
To use your own analogy, your house doesn't have any foundations; there's no point in worrying about the roof.
Brianwarnock
Retired
- Local time
- Today, 05:46
- Joined
- Jun 2, 2003
- Messages
- 12,701
Well it looks like Paul's thread has been hijacked so I dug into my briefcase and pulled out my management hat that I wore in a previous existence, so don't press me on this but can you run the queries that produce the needed info at periodic intervals but write the results to a table, make table or append as necessary, this table does not cantain anythink but transient data. On anybody starting up a simple report of the info can be produced without any calculations, further the calculations aren't done over and over as people come onto the system.
OK guys will it work or not?
Brian
OK guys will it work or not?
Brian
M
Mike375
Guest
Brianwarnock said:
Brian,
That is essentially what I am doing. However, what I have found is that the SetValue (and I assume the code counterpart) is the most economical or least energy consuming way to transfer the data, in some cases. Copy and paste seems to consume more energy that append queries.
For me at least, it works much better if a very complex query gets its data to another table via SetValue than appending, especially when there are lot of them to do in one go.
Large tables (as in number of records) also seem to consume far more than I think most people on this forum think. One of the reasons that our telemarketing uses a table for each category of prospect is that each time a call is completed and the telemarketer clicks "Next Prospect" then the data base updates on each call and he sees the figures at the bottom off his screen comparing his results to the goals as of the last call. Stick it all in one table with queries to select and you start to see the computer bog down as the calling continues.
Mike
M
Mike375
Guest
I just tried timing my data base.
I just made a little macro that would run one of the macro you click on following a telemarketing call and then move to the next record and repeat. I set it at 100 repeats.
I also "clicked" on the two labels that mean the data base will update for both the category of prospect being done as well as the other 19 categories. In other words actual calls made are being measurfed against pre set goals and they are both updating on each click of Next Prospect.
For my system that uses a lot of small tables, that is a table for each category it took 153 seconds to run through a 100. Although most of this time was for the last 25 records where the computer slows right down. This system has the various goals for each day made as a new record. Although some "calculation" is required as the query is "selecting" on dates.
For my system that "calculates" everything it took 263 seconds to run through the 100. Most of this time was used getting from record 50 to 100.
For my own practical purposes both systems are similar in speed as the non calculating system is not that much faster for the first 40 records. In calling time that is about 80 minutes at which the telemarketer will stop, close the data base down and reopen after his smoko.
This was on a Dell laptop Inspiron 5100
After the calculation system had done 100 records everything on the data base was virtually at snails pace.
The sytem with the tables has about 180 records in each of the 20 tables and that represents each day from early January. A single table that holds all of them has 20 X 180 records. Both the large table and one of the small tables was involved in what I did. As sort of half and half deal. The small table is behind the update for the category of prospect I used and the big table for all of the objectives Vs Results
Mike
I just made a little macro that would run one of the macro you click on following a telemarketing call and then move to the next record and repeat. I set it at 100 repeats.
I also "clicked" on the two labels that mean the data base will update for both the category of prospect being done as well as the other 19 categories. In other words actual calls made are being measurfed against pre set goals and they are both updating on each click of Next Prospect.
For my system that uses a lot of small tables, that is a table for each category it took 153 seconds to run through a 100. Although most of this time was for the last 25 records where the computer slows right down. This system has the various goals for each day made as a new record. Although some "calculation" is required as the query is "selecting" on dates.
For my system that "calculates" everything it took 263 seconds to run through the 100. Most of this time was used getting from record 50 to 100.
For my own practical purposes both systems are similar in speed as the non calculating system is not that much faster for the first 40 records. In calling time that is about 80 minutes at which the telemarketer will stop, close the data base down and reopen after his smoko.
This was on a Dell laptop Inspiron 5100
After the calculation system had done 100 records everything on the data base was virtually at snails pace.
The sytem with the tables has about 180 records in each of the 20 tables and that represents each day from early January. A single table that holds all of them has 20 X 180 records. Both the large table and one of the small tables was involved in what I did. As sort of half and half deal. The small table is behind the update for the category of prospect I used and the big table for all of the objectives Vs Results
Mike
R
Rich
Guest
Why not just use a Report/subReport or Form/Subform
M
Mike375
Guest
KenHigg said:
1 form for all categories = bogging down during telemarketing....unless update after each call is turned off. I form and 1 table with say 300 records is much quicker than one form based on a table with 6000 records and the queries to separate the categories. No....there is no big difference when you just open such a form but when it is done everytime the telemarketer ckicks Next Prospect it makes a big difference.
As to Kevin_S comments I simply told the person what I do. He appears to have more than enough knowledge to make his own version. If I had to make that from scratch then I might have one form based on query with all records and each time the form opened it would be for the selected records. I might also do it with a macro running a bunch of append queries. I choose to do it the way I do because the basics were already made.
I still wish someone would give me a better way to sort A to Z across a row than Pat Hartman's answer from last month of....You don't do that in Access so you are on your own......well at least I did it but a better way might make it easier for me to make another version which is does not relate to this data base or insurance.
I am beginning to learn why the life insurance company rate/quote sytems have all their limitations.....I guess it is a case of "we do things this way in Access and if what is wanted does not fit then too bad.
Mike
R
Rich
Guest
Franknstuff
More I know Less I know
- Local time
- Yesterday, 21:46
- Joined
- Apr 28, 2004
- Messages
- 54
Good luck
Good Luck Pauldohert,
Once they get going its real hard to get any answers that "You" asked for.
Good Luck Pauldohert,
Once they get going its real hard to get any answers that "You" asked for.
R
Rich
Guest
that's because the posters with sensible answers keep getting interruptedFranknstuff said:
M
Mike375
Guest
Rich said:that's because the posters with sensible answers keep getting interrupted
Not so. When Franknstuff asked his question on DB size most off you went off at a tangent when I answered his question with what he requested, that is, the size your data base.
So far none of you has even attempted to answer Pauldohert's question except for Brianwarnock and myself.
If my postings interupt you then I suggest you check your limitations. In my case I am fortunate and I only have to be involved when I want to be involved. Also my computer is pretty special as it has a scroll bar.
But why don't some of you have a go at answering Paul's question.
Mike
R
Rich
Guest
learn to read!
- Local time
- Today, 00:46
- Joined
- Feb 19, 2002
- Messages
- 43,474
- What am I? chopped liver!
M
Mike375
Guest
Pat Hartman said:
But later he asked "how" as opposed to the general question and ..did anyone else encounter such a problem and how would they get around such a problem.
Franknstuff
More I know Less I know
- Local time
- Yesterday, 21:46
- Joined
- Apr 28, 2004
- Messages
- 54
Good Luck
See what I mean.
See what I mean.
M
Mike375
Guest
Franknstuff said:
Franknstuff
The difficulty with Paul's probem is that some people can only copy and paste code
They laughed at the solution I offered but they offered no alternative.
Having said that, I think the person that does the data base has a type of persistence about them. In other words.....personalities on Forums won't stop them.
As much as I disagree with people like Rich and Pat Hartman I also know the hours they would have put in.....I also think they realise I am not a fool and have done a few hours myself.....maybe more than them!!!
But at the end of the day, to do the data base you just have to wade through a lot of mud, weeds, quicksand and etc. and etc.
In short, easy answers simply don't exist with the data base.
Persistence and DB go together.
Mike
Mile-O
Back once again...
- Local time
- Today, 05:46
- Joined
- Dec 10, 2002
- Messages
- 11,316
Mike375 said:
And rightly so. Without any substantial details nobody can make an assessment of the task at hand in order to offer an alternative.
Why?
Yes, you probably would have put more hours in than them as your designs, structures, and processes, as you have outlined and described require ongoing maintenance in order to ensure they work. Pat, Rich, myself, and others would ensure that - with the database being properly normalised - there would be no need for ongoing maintenance as, when a new instance of a class is added to a database (i.e. a new employee) their details fill a database record and not a database field and/or table.
Nope; just think logically and approach the design in a specific order. Table design, query design, form design (binding to queries), more queries as necessary, and report design. Nowhere...Nowhere...Nowhere... should there be any macros (other than an autoexec if required) - they are slow, offer little flexibility, and provide no means of error trapping/handling.
R
Rich
Guest
don't bet on itMike375 said:
