Incremental queries how to automate

[MrTibbs]

Access '97.

We have daily data being added to a series of tables. Is there any way to build a daily running summary of this data automatically?

tblEvents containing
date time event duration cost

summarised into

tblEventSummary
date event eventcount totalduration totalcost

A summary table will really speed up the queries as events can occur hundreds of times a day and we need to compare and report across months of data.

I can build an append query to the summary table quite easily but am not clear on how to automate the daily append nor how to pass variable parameters (yesterdays date) each day

 

[dcx693]

If you just want to calculate a summary of daily data and append that to a "summary" table, you can do that.

Create a totals query to get the summary of your daily data. Then use that query as the source for an update query that will append the daily data to the summary table. Save the update query so you can run it by double-clicking it or calling it from code.

What do you mean by "how to pass variable parameters (yesterdays date) each day". If the field is called "date" then the summarized field would just show the date for that day. The update query would just pass the field data to the summary table.

 

[MrTibbs]

How to automate?

Thanks for the suggestion regarding date.
I can determine the required dates but still do not know how to automate the process so that it runs every day without human intervention.

Could I schedule database runs that read /cmd, does the update and then closes the query database or is there a more elegant approach?

 

[dcx693]

I don't know how to automate the process. I know you can use the Timer event in a form to have code run at particular intervals, but I don't know how accurate or reliable that is for a period like 24 hours. Hopefully, someone will be able to answer.