Techniques in creating a report

[hlam]

I have a database that stores each staff's prodivity in terms of volume over
time. I need to create a report each month, day 1 to day 31, to management
on staff performance by department and then shift.



In order to do this I have created an event button that will do the
following:-



1 an append query to select data from the master table
base on a specified date criteria. The data is stored in TableA

2 an update query to TableA to calculate the productivity
for each staff.

3 A pivot query with date as the column heading

4 A query to append pivot to TableB onto its appropiate
Day fields (1-31)

5 In order to provide a grand total for each shift I need
to create a subreport by repeating from 2 ¡V 4 and stored in a different
table, say TableB_Sub because I need to have a breakdown on subreport on
each job code as well as its parent.

6 A report from TableB, that matches up names and some
other job code description used to display for reporting. Report is grouped
by department, shift, name and job description. Subreport from TableB_Sub is
linked on department that also has same grouping as its parent table.



There are a lot of queries in this procedure that takes up a considerably of
time to process. I want to know if there is anyone who can give me some
adivse how I can speed up this process.



This report needs to show:

Department, shift, name, job-code, productivity (volume/time) from day 1 to
day 31.



Any advise is appreciated