What is the best practice- reports & queries 2

[drkhelmt]

My question to the community is, what is the best practice reguarding queries and reports.

To explain- you can set up queries, then use the query as the record source of a report. Or, you can just build the query directly in the report. I have just built the query into the report, unless I think I might want a similar query result for a differnt report, then I make a query and use it.

What's best?

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[MajP]

In the A2k Desktop Developers Handbook (I do not have it on me to verify) there is a long discussion on the Access query optimizer and optimizing queries. Basically when you write a query it is compiled in an optimized state. This is a pretty involved algorithm. I do not think that your queries on a form or a report are optimized, definately the ones you write in code are not optimized. If I am worried about performance, I will write seperate queries. Here is some good reading, and supports what I said:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q209126

Save the SQL statement as a query

If the RecordSource property for a form or for report is set to an SQL statement, save the SQL statement as a query and then set the RecordSource property to the name of the query

 

[jsteph]

Andrew,
If you have a report that will be run with various parameters, that will change either daily or each time the report is run, then in my opinion you create the sql in code, and set the .recordsource property for the report.

It's true that the optimizer has to run each time the query/report runs. But the peformance hit is generally going to be minimal in terms of percentage of time to run the report. In other words, maybe, maybe 1/4 second (it's probably significantly less than that) to do the optimizer at runtime is added to the opening of the report. Many reports take several seconds, some even minutes. But even the ones that pop-up in sub-second time--I'd sacrifice the miniscule optimizer time for the ease of coding.

One area where you want to use saved parameter queries is in a code loop--if you're looping through a recordset over thousands of records, and running an Update or Insert query for every record based on a value from the recordset--here you'd want to parameterize it and use the saved querydef object--that's where the time adds up.
--Jim

 

[psemianonymous]

For the record:

o I love queries for everything. If you're going to have a base SQL statement, put it in a query. Even if you know you're going to build a custom SQL statement, still put your base "SELECT etc from tableETC" in a saved query. You'll be surprised by the load this takes off your brain and kind of sticks an extra layer between the data and your reports.

o I heavily use the "WhereCondition" property of every report and use a base query like "qryRptContosoEtcEtc" and then put the WhereCondition:="ETC_ID = 7". This is better than using parameter queries because you can make the parameters OPTIONAL, which isn't such a big deal with only one parameter, but when you have like 8 parameters, it's a BIG DEAL. Anyway.

o Also I recommend a "filter form" for each report you're going to open. I tried making a giant single "filter form" for all my reports but it was a monster. Make one form per filtered report, trust me. Alternately there are a million third-party products that filter Access reports, literally a million billion(!!!). Don't try and sell your solution, trust me (again).


Alternately, use Crystal reports which has its own filtering mechanism which is awesome.