Query v Report / Speed

[Wayne-I-M]

Hi

I have a large report to run from a query. To make it simpler for users to
understand the reports I have various calculations to do. Quite a lot ?

Is it quicker to do the calculations in the report or query.

Thank you

 

[Jeff Boyce]

Wayne

What have you seen when you try this? There are so many factors that may
influence the "speed" that it will be tough to predict -- you might learn as
much from trying both ways!

By the way, a common approach to building reports is to create a query that
returns the data, then build the report based on the query, not a base
table. In this instance, the query will HAVE to run first, before the
report formats the data for printing, so the query will run faster.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Steve Schapel]

Wayne,

The only way to know for sure in your specific scenario would be to test
it. However, my understanding is that in general it is more efficient to
do the calculations on the report rather than the query.

 

[Wayne-I-M]

Thank you Jeff

I will take your advice -

It is a report showing group booking from an internet form and there are
"zillions" of combinations. - Also I will run it though an apend first to
correct any obvious mistakes (lower case postcodes, non captial case names,
format the dates of birth, etc, etc)
http://www.folgarida.co.uk/Page_Room4.asp


I thought it would be a good idea to just have a report that we could keep
on hand and maybe send a copy to the client group leader prior to
confirmation - mistakes do happen.

Thanks again.
--
Wayne
Manchester, England.

Wayne

What have you seen when you try this? There are so many factors that may
influence the "speed" that it will be tough to predict -- you might learn as
much from trying both ways!

By the way, a common approach to building reports is to create a query that
returns the data, then build the report based on the query, not a base
table. In this instance, the query will HAVE to run first, before the
report formats the data for printing, so the query will run faster.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Wayne-I-M]

Ha Ha

Thanks Steve - the opposite answer to Jeff.

No worries I will try it both ways.

 

[Jeff Boyce]

Actually, not the opposite.

The calculations' speed will depend on a number of factors, including where
the data comes from, what's indexed, whether the functions used in the
calculation are "local" (as well as the data), ... and so many more.

My comment about the query running first was just that ... if you build a
query, then use it to feed a report, the report will have to take longer
than the query alone.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Wayne-I-M]

HI Jeff

I will do the calculations in the query. Partly to follow your advice -
which is good and partly as I prefer to be able tp see whats "going on" and
view the data beforehand.

I think I am like most access "types" I tend to design reports to fit the
expected data. So if I can see the data first then I'll design a report that
goes right to second fix.

Thanks again