One Query w/many different results

[beckie1234]

Is there a way to write one query that will work for different criteria and is run by different buttons to produce bar graphs or a basic report depending on the button the user clicks??

 

[KeithG]

One query with different criteria? Why cant you use two?

 

[The_Doc_Man]

I'm with Keith on this one. The problem with choosing different criteria splits into "different values, same actual comparisons" - in which case look into the Access Help files for "parameter queries" - or the queries are truly different. In the latter case, a stored query with variable criteria becomes far less efficient due to invalidation of the pre-analysis Access does when it stores a query. You don't really want to invalidate the scan scheme because that leads to what is called a "relation scan" - the slowest type of query there is for any given configuration.

 

[beckie1234]

The only reason I was wondering is because it is for a project and it was suggested that I could use one query based on the specifications picked by clicking on a button with different criteria. Basically one query would be run by 41 different buttons with the one constant always being between begin date and end date.

My idea was to have a specific criteria query for each button the user pick, but I was told there was a quicker and easier way to do this but I am not sure how to go about this.

 

[gemma-the-husky]

include in your query a column called

needthis: includeme(optional parameters from the row)
with criteria true

now you also need to have a module function

function includeme() as boolean

in this function you can test whether you need to include the row according to the content of the row/ and any conditions ou need held outside the query.

this will give you a tailorable query.

you can presumably open appropriate/different forms or reports with the query to suit the button you clicked

 

[beckie1234]

Could you put that in simpler terms. I eventually need it to produce 40 different reports. Each report will be produce from each button and will be shown in a bar graph showing most to least. They start with line then drill down into machine center, then drill down even further for the category, and then the final report (Reasons) will be based on reasons a user types in. It will be shown in a basic report style.
Choice for each of these selection come from cascading combo boxes
on a form.
Once that info is entered it goes to tblDowntime where the data is stored and that is where I want all these reports to generate from from.
Line=>MachineCenter
MachineCenter=>Category
Reason