"Daisy Chaining" Queries

  • Thread starter Thread starter MK
  • Start date Start date
M

MK

Here is my Scenario.

I have created a DB that functions as inventory control, purchasing and work
orders. All the tables are optomized and all works well. Now I want to
retreive specific data. I have set up a form to allow me to select the
parameters for the data I want. The form Calls a report that is based off of
a Query and the selection fields are Date (beginning and ending), Customer
number, Equipment group, Operation Code, Op Code group, Mileage Etc..... for
each of these selection criteria there can be a value or it can be "is
null", so the only way I could figure out how to "mine" the data was to
build a query of a query of a query.... Basically I have a query for each
Selection criteria...

My question is this... Is there a better way? Depending on the amount of
records the report can take awhile to generate....
Any suggestions would be helpfull....
 
No problem stacking queries on top of each other: using a query as an input
"table" for another query is quite common where the usual joins are not
powerful enough, or where you need to requery multiple aggregated values.

In some cases, you may find it useful to create a subquery. Details in:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

For good performance, make sure the fields you sort or place criteria on are
indexed. Avoiding unnecessary criteria also helps, e.g. if you want to just
ignore a text box when it is null then you may want to lose if from the
WHERE clause. To do that, you might want to create the query statement
dynamically, instead of using a saved query. You can then use this statement
as the RecordSource for a form, or as the SQL property of a QueryDef, e.g.:
strSQL = "SELECT ...
Me.RecordSource = strSQL
or
CurrentDb().QueryDefs("Query2").SQL = strSQL
 
Here is my Scenario.

I have created a DB that functions as inventory control, purchasing and work
orders. All the tables are optomized and all works well. Now I want to
retreive specific data. I have set up a form to allow me to select the
parameters for the data I want. The form Calls a report that is based off of
a Query and the selection fields are Date (beginning and ending), Customer
number, Equipment group, Operation Code, Op Code group, Mileage Etc..... for
each of these selection criteria there can be a value or it can be "is
null", so the only way I could figure out how to "mine" the data was to
build a query of a query of a query.... Basically I have a query for each
Selection criteria...

My question is this... Is there a better way? Depending on the amount of
records the report can take awhile to generate....
Any suggestions would be helpfull....
Click to expand...


Another (more efficient) approach is to use VBA code to loop through
the controls on the Form, building up a SQL string using only those
controls which contain a criterion. You would then set the Report's
Recordsource to the resulting string.


John W. Vinson[MVP]
 
Back
Top