A
Author
Our company has a report that is run a dozen times by each department,
totaling about 60 times a day. What the report does is display all the open
orders for our plant- the query behind it pulls data from three linked
tables: Item Master contains information such as Quantity on Hand and
Inventory Location, and the other two tables are the Header and Line Item
details of Sales Orders. I would like suggestions on ways to decrease the
“run time†of the report- it currently takes three to five minutes each time
it is run. In part this is due to the fact that Access “talks†to another
program with ‘live’ information, i.e. Quantity on Hand and recently entered
Sales Orders. However, I suspect it is due to all three tables being
exceedingly large and containing international sales orders and obsolete part
numbers, when we only need the sales orders and part numbers that we make.
One option that I have considered is creating a query that updates a table
with only the open orders for the plant, thereby decreasing the amount of
table data and reducing the amount of “thinking†that the report would do;
but this would in turn require regular running of the query to refresh the
information, which could prove counter-productive. I suspect there is a
better way. I tried to describe the structure of the database- please let me
know what details I can provide to clear up any confusion. Using Access
2003. Thanks for reading, and for your assistance.
totaling about 60 times a day. What the report does is display all the open
orders for our plant- the query behind it pulls data from three linked
tables: Item Master contains information such as Quantity on Hand and
Inventory Location, and the other two tables are the Header and Line Item
details of Sales Orders. I would like suggestions on ways to decrease the
“run time†of the report- it currently takes three to five minutes each time
it is run. In part this is due to the fact that Access “talks†to another
program with ‘live’ information, i.e. Quantity on Hand and recently entered
Sales Orders. However, I suspect it is due to all three tables being
exceedingly large and containing international sales orders and obsolete part
numbers, when we only need the sales orders and part numbers that we make.
One option that I have considered is creating a query that updates a table
with only the open orders for the plant, thereby decreasing the amount of
table data and reducing the amount of “thinking†that the report would do;
but this would in turn require regular running of the query to refresh the
information, which could prove counter-productive. I suspect there is a
better way. I tried to describe the structure of the database- please let me
know what details I can provide to clear up any confusion. Using Access
2003. Thanks for reading, and for your assistance.