on demand query refresh
- Thread starter Tskutnik
- Start date
Thanks ken. I'm sure the question is confusing. Simple response for now: I have a big database with a load of queries that calculate everything under the sun.
The underlying table data is changed occasionally which seems to auto-trigger all the queries to recalc. Issue is: I only need to apply the updated values in the tables when a report is requested, which is infrequently and on selected data. The thought is the tables are updated through the day with some select queries auto-run that we can use for proofs, but the process of all the complicated math queries would be done in an overnight cycle. That data would maybe be saved off to a table and ready for reporting and privide quick response times when reports are run. I'm not sure if the full process yet. Still working that out.
Thanks for the help.
The underlying table data is changed occasionally which seems to auto-trigger all the queries to recalc. Issue is: I only need to apply the updated values in the tables when a report is requested, which is infrequently and on selected data. The thought is the tables are updated through the day with some select queries auto-run that we can use for proofs, but the process of all the complicated math queries would be done in an overnight cycle. That data would maybe be saved off to a table and ready for reporting and privide quick response times when reports are run. I'm not sure if the full process yet. Still working that out.
Thanks for the help.
Action queries, the kind that can alter data, typically indicate a poor table structure or a developer who doesn't fully understand how databases should work.
So far so good, that's how things should be done
Nope. You don't store calculated values. Your reports shouldn't be based on those tables where you are storing the calculated values, but on the queries that are doing the calculation. You do that and there's no need to update anything, the user selects a report to run, the underlying query runs and produces the correct dataset and the report is generate from that.
Action queries are for the weak.
So far so good, that's how things should be done
Nope. You don't store calculated values. Your reports shouldn't be based on those tables where you are storing the calculated values, but on the queries that are doing the calculation. You do that and there's no need to update anything, the user selects a report to run, the underlying query runs and produces the correct dataset and the report is generate from that.
Action queries are for the weak.
KenHigg
Registered User
- Local time
- Yesterday, 19:48
- Joined
- Jun 9, 2004
- Messages
- 13,327
Seems a bit premature when we don't know anymore than we do about his db -
Thanks for your help on this guys. I think I'm starting to get this through my thick head. I always assumed that all queries are auto refreshed when the underlying table data is refreshed. At least that is what it felt like based on my (probably lousy) query design.
Since I want the report/query to pull and calc just specific data (e.g. just one account's worth of data out of the 10,000 accounts in the database) I'll use the report parameters as the query parameters and then just pull what I need.
Seems if I just limit the calcs to what I need I eliminate the calculation for all accounts. Therefore if my query design is better this goes away.
If this the right basic approach?
Since I want the report/query to pull and calc just specific data (e.g. just one account's worth of data out of the 10,000 accounts in the database) I'll use the report parameters as the query parameters and then just pull what I need.
Seems if I just limit the calcs to what I need I eliminate the calculation for all accounts. Therefore if my query design is better this goes away.
If this the right basic approach?
I didn't fully understand what you are going to do, but it sounds like you are adopting what I said--basing the report on a query and not on the table.
If so, that's correct. This is why you don't store calculated values--why go through an unnecessary intermediate step (putting calculated data into a table field) when you can just run the report and get the data you need directly from the query?
Maybe the following is a better question to help me understand this broadly. The forms and reports I have point to a few selected queries, so that a single massive query can service a whole bunch of forms and reports. The obvious advantage is that is I chance a calc that serves multiple forms/reports I only have to change it once.
Is there a best (better) practice for the underlying query management? I could obviously write a query within each form and report and then the query would only run when that specific data is called.
I have sort of taken the other approach - if I have a huge query that served many forms and reports it is fewer queries to write and maintain.
Maybe I've been thinking about this a little wrong? Maybe I should have more and smaller queries, each written not as a stand alone query, but from within the Form or report. I guess I could have separate modules or the calculations, so they are all referenced, and not done with the queries themselves?
... or maybe this is the ultimate judgment call that a developer has to make about how to setup their database? If that is the case I guess the light is finally going off in my head and I can proceed accordingly.
Seem this has turned into much more of a "smart database concepts" posting, which is probably what I needed.
Thanks again for your advice.
Is there a best (better) practice for the underlying query management? I could obviously write a query within each form and report and then the query would only run when that specific data is called.
I have sort of taken the other approach - if I have a huge query that served many forms and reports it is fewer queries to write and maintain.
Maybe I've been thinking about this a little wrong? Maybe I should have more and smaller queries, each written not as a stand alone query, but from within the Form or report. I guess I could have separate modules or the calculations, so they are all referenced, and not done with the queries themselves?
... or maybe this is the ultimate judgment call that a developer has to make about how to setup their database? If that is the case I guess the light is finally going off in my head and I can proceed accordingly.
Seem this has turned into much more of a "smart database concepts" posting, which is probably what I needed.
Thanks again for your advice.
Here's one more that will keep you out of trouble:
Forms that directly interact with data (add/edit/delete) shouldn't be based on queries, they should be based on tables.
1 form, 1 table. When you have a one-many relationship you have a main form (one) and then a subform (many) which itself is based on just a table. Reports can be based on queries to generate the data you want them to show.
Forms that directly interact with data (add/edit/delete) shouldn't be based on queries, they should be based on tables.
1 form, 1 table. When you have a one-many relationship you have a main form (one) and then a subform (many) which itself is based on just a table. Reports can be based on queries to generate the data you want them to show.
