Increased Speed?

  • Thread starter Thread starter Stefan
  • Start date Start date
S

Stefan

I have a patient aging report 30 days 30-60 60-90 90+.

Each of the date ranges is based on a seperate query. And
each is a sub report in the main patient aging report.

Takes several minutes to open this (currently 14 pages)
report, and it is also slow to move between pages. In the
task bar when I open the report the query runs quickly but
it seems to be hanging while it is formatting the report.

Is there any tricks to getting this operate faster?

Thanks in advance
 
Stefan,

Hard to say definitively without knowing more detail. But on the face
of it, I would expect the use of subreports to be unnecessary, and also
separate queries. A Crosstab Query may be a promising avenue to explore.

There are other factors too. For example, using correct indexing of
your table fields, and applying selection criteria as early in the
querying process as possible, especially if you have a large volume of
base data. But I would suggest tackling the multiple query/subreport
structure first.
 
I agree with Steve S. It would help to know your table structure. A single
crosstab or totals query should be able to work for you. For instance to
view all sales qty for various date ranges by Product:
SELECT tblSales.Product,
Sum(Abs(DateDiff("d",[SaleDate],Date()) Between 0 And 30)*[Qty]) AS
Age_0_30,
Sum(Abs(DateDiff("d",[SaleDate],Date()) Between 31 And 60)*[Qty]) AS
Age_30_60,
Sum(Abs(DateDiff("d",[SaleDate],Date()) Between 61 And 90)*[Qty]) AS
Age_60_90,
Sum(Abs(DateDiff("d",[SaleDate],Date())>90)*[Qty]) AS Age_90_Plus
FROM tblSales
GROUP BY tblSales.Product;
 
In addition to the other suggestions, check the 'keep together' property for
all of your report sections.
They are probably all set to 'Yes'.
Set to 'No' for any section that you don't really need all the detail
together.
Then in 'Sorting and Grouping' set 'Keeep Together' to 'With First Detail'
for the appropriate groups.
 
Back
Top