Why Is Saving Query Results Bad?

[Reese]

Everyone keeps saying that saving the results of a query is a bad idea and pointless. I understand that it CAN be bad (if you have something that is going to constantly change), but I still don't understand why it is ALWAYS a bad idea.

For example, I am creating a database for booking education programs at a zoo, scheduling them and reporting on income, numbers of participants, etc. To calculate the total cost of a program, the program price is added to a millage fee and possibly an additional auditorium fee. Once that calculation is made, the event has past and payment complete, why should the database have to rerun the calculations every time it generates an income report?

Especially if that calculation has to be made for every education program (of which we book hundreds each year). Then, in order to compare yearly income, it would have to do that for every single year that is part of the report.

Instead of doing a simple three part addition possibly THOUSANDS of times to generate a income comparison report 3 years down the line, why not save the calculation result at the end of the program, then save the calculation result of total income at the end of the year?

Plus, if anything got lost or screwed up in even one single field of any of these programs, the entire calculation would come up as an error and/or be incorrect.

This is just one example, let alone many others that I think saving would be best. Could someone please tell me why saving is bad?

 

[plog]

95% of the time you shouldn't store calculated values. But the thing is, we have to say you never do it because people asking for advice will see that 5% and convince themselves their situation is part of that 5%.

Data integrity is the main reason you don't store calculated data. The underlying data can change (especially in systems with multiple users).

This means you can't trust the calculated value that have been stored and will have to perform a series of updates to those calculated values, thus making their storage moot.


Never trust users, data or unproven assumptions about the way your system works. Doing calculations when you need them is one way to avoid erroneous data.

 

[Galaxiom]

Why are you worried about the computer doing calculations thousands of times? Modern computers can perform millions of calculations in a second.

If calculations are very complex and take a significant time then it does make sense to store the results. But in most cases where the table fields are properly indexed the difference between retreivng a stored calculation and recalculating it is only fractions of a second.

 

[Reese]

I understand that my situation may not fall under the 5%, but it really confused me why everyone immediately dismissed any requests to save queries out of hand with out even considering why it might be necessary.

I also realize that addition isn't exactly processor intensive. It may sound counter-intuitive but the reasons why I want to save the results are to save storage space and prevent errors from popping up.

My idea (if it is possible and if it makes sense) is to keep the query as a pure calculation until the program has been both paid and has occurred. After that point the data involved in the calculation would not change (unless by accident). By saving the data I would then be preventing accidents from impacting past entries. And I want to ensure that if our program's prices change they won't change past entries that use the old prices. I don't think it would without opening the old entry and thus running the relevant VBA again, but I want to be sure.

If possible, I would like to have a command sequence that I could run and would wipe out nearly all of the data from entries over 2 or 3 years old. It would only keep contact info, the date, type of program, amount paid and any special notes. This would save storage space, since we wouldn't need all the other info (which is quite a lot).

There will also be several other types of queries that involve copying over information from past entries into new ones (e.g. autocopying contact info from last year's invoice into this year's if it's the same client). This copied information will need be viewed and possibly edited (e.g. if the contact person, but not address, has changed) through multiple forms and/or reports. Will that involve saving information?

Please let me know what (if any) of these goals need saving vs. regular queries and which ones are possible. Thanks.

 

[gemma-the-husky]

it really doesn't take long for computers to recalculate values each time.

so why would you want to

well - one reason is that in some cases a calculation might take a lot of processor time, and once it is calculated it never changes - in which case it may be worth storing the calculation result. eg - the VAT value iof an invoice.

another reason is that the underlying data may get deleted at some point, as you say. say you have a stock system. normally the stock quantity on hand is calcluated by adding together all transactions. so say at 1st Jan, you count the stock, and process an adjustment to balance the stock. Well now it might be appropriate to store the quantity on hand as of 1st Jan, so you can disregard all older transactions.

the problem with errors tens to work the other way. let's your program stores a continuous count of something. that means that EVERY process that could affect the count needs special code to make sure the total gets updated correctly in repsect of that process. that is in general rather harder to do than simply re-evaluating the total as and when it is needed.

having said that, data deletion is really something that shouldn't happen either. having spent a lot of time gathering data, it is generally not a good idea to delete it at all. just use a data range, or some other filter idea to ignore old stuff.

 

[Reese]

I understand what everyone is saying but I'm still not sure as to whether I fall under the majority of cases where regular queries should be used or saves should be used. Perhaps having specific questions would help:

Is it possible to, under a certain condition or command, have a field value switch from the result of a normal query (not saved) to a saved result of that query?

Is it possible to, under a certain condition or command, have a field value change from the result of one normal query to another normal query (neither one being saved)?

Right now the database is 3.5MB when compressed. It has about 2/3 of the tables, form and report templates needed and only has about 15 entries total. Each year there will be between 400 and 600 total. I'm a newbie but a quick guess for 5 years down the road is about 1.5GB compressed. Does that sound like a reasonable guess?

I will need to use queries to find specific entries based on a search function (literally typing in a name and it search for a past entry). If I want some of the information from the old entry to be applied to the new entry will it be referencing (just pulling and displaying) that information or will it actually copy over?

If the above situation references the old entry without copying it to the new one, will that information be editable? (E.g. if the contact or location information has changed since the last event.)

There are several VBA events in various forms or reports that set the value of a field based on the results of option boxes, check boxes and/or combo lists. If I changed the conditions of these event procedures (Option A results in text box B being set to new value C instead of old value D), will the new conditions apply to past entries if they are referenced using other forms or reports? (E.g. if the price of a program changes I don't want the programs from a year ago to then be calculated using the new price.)

When opening up a form or report there is the series of buttons on the bottom left of the window that allow users to move from one entry to another within that form. Is there a way to deactivate those buttons? (I want to prevent users from accidentally switching to editing entry A when they should only be editing entry B. If they do accidentally edit the wrong form, that would change the results of a regular query when the data shouldn't have actually changed.)

I know that's a lot but these are things that I'm trying get a better understanding of what Access does and how it meets my needs.

Thanks.

 

[plog]

Your new questions are scarier than your intial one. It sounds like you built your database, then you did some research and are learning you did it incorrectly and are now seeking out justification for keeping it as it is.

The two questions you asked about query possibilities: yes those things are possible. But just like saving calculated values is possible, its probably not the right way to go about things.

As for your size estimates, they sound way off. 600 records will most likely not put your database over a 100 MB, much less a gig. Unless of course you are storing every calculation ever.

Then you ask about 'copying' data around. You shouldn't copy data around in a properly structured database. You should reference it and use it when you need it from where it resides.

If you want to upload your database we will be happy to take a look at it, but the more you talk about it the more you sound like the 95% of people who should structure your data properly.

 

[Reese]

While I may have created the database with extra fields, expecting to save data, I'm not married to the idea. It's just that logically (in my lack-of-Access-experience mind) saving is the best way to do certain things. I am perfectly willing to do it other ways so long as my goals are met.

I literally need to leave right now but I will upload the database and explain my goals (vs. asking how to meet them) when I am back in the office Saturday morning.

 

[Reese]

First thank you guys for bearing with me on this. I’ve attached a stripped down version of the database, with just ZooMobile table, reports, forms and queries. The complete database is too large to post and the ZooMobile part of the database is likely to be the most complex anyway.

So, stepping back a little, here’s the purpose of this database:

-Steam line the booking, scheduling and billing of zoo programs
-Track and summarize reports on income, client diversity, number of participants and zoo resources

I have only created things relating to the first goal so far. I want to handle all of the queries, etc. within this process before I get to the second one. Like I said before, I may have created the tables with saving information in mind, but I am not married to the idea.

What I am married to is all of the information that is displayed in the forms and invoice reports. I don’t care where that information is stored or how it is processed, just how it is entered and how it is viewed.

Here are the more specific goals or situations that relate to the questions I have asked so far:

-We have our regular program pricing for ZooMobiles based on type and length, but then in a specific organization is paying for the program there is a different pricing system based on the number of students.

- The “Total Price” text box in each of the forms and reports needs to display the correct price according to the situation.

- I want it to still be displayed in the same box, however, so that users and clients aren’t confused

- When I get to the reports like “annual income” the database needs to not be confused between the two queries and take the appropriate one into account
​

-Part of stream lining the booking process involves referencing past programs for the same client.

- Some kind of search function that at least indicates if a program has been booked by a client in the past, as or after the organization, event and/or contact name is typed.

- After this has been indicated, some kind of event or command that will automatically display the appropriate information in the appropriate boxes.

- Ability to change that information (in case the contact person has changed), preferably while keeping a record of the old information as well as the new

- A point of clarity: There are duplicates of some fields (Other Comments, Animal Lists, etc.) This is because one is manually typed and associate with the current program, the other is the one that somehow displays the information that was manually typed for the last program.
​

- Preventing accidental corruption of past entries. This is why I was thinking of saving query results once a program has past, etc. Can I do the following instead?

- Use VBA or something to lock the relevant information (Millage Fee, etc.) once a program has been paid and past.

- Somehow disable those navigation buttons on the bottom left of the window so users don’t accidentally shift from event 1 to event 2
​

-Conserving storage space. I had overestimated how much space it would take so that’s no longer an issue.

Thank you once again for your help.