Re-Using Queries

[Xeseus]

I am new to Access and would like to know the best way to set up queries for a database that uses 20 - 30 reports. Will it run better with a query built for each report or should I re-use queries for more than one report if possible? I am concerned that if two separate reports are run using the same Select query using different parameter values that there will be trouble. All my queries are Select queries.

 

[jrbarnett]

Provided you make your queries general enough that you can pass across the parameters as the WhereClause argument in your OpenReport query, you'll be fine.
Where you may run into trouble is if you need to rewrite the SQL completely for different reports.

John

 

[Xeseus]

Yes, the parameters are fairly simple: a date range, and a company ID field. Also, each user has their own front end.

 

[jrbarnett]

Just do something like:

DoCmd.OpenReport "rptMyReport", acViewPreview, WhereClause:= "[Companyid]='" & Me!txtCompanyID & "'"

That's fine. In faq702-3619 I've got instruction on dynamically building a where clause for reports which is very applicable to you.

John