Queries and multiple criteria (1 Viewer)

[beckie1234]

Is there a way to produce one query that will produce several results that display in a report that is generated from a button? The only difference is the criteria.

EX:
SELECT tblDownTime.dtDate, tblLine.lineName, Sum(tblDownTime.dtDowntime) AS [Total Time Down]
FROM tblMachCent INNER JOIN (tblLine INNER JOIN (tblCategory INNER JOIN tblDownTime ON tblCategory.catID = tblDownTime.catID) ON tblLine.lineID = tblDownTime.lineID) ON tblMachCent.machID = tblDownTime.machID
WHERE (tblDownTime.dtDate) Between [Forms]![frmDTGraphs]![begin] And [Forms]![frmDTGraphs]![end]))
GROUP BY tblDownTime.dtDate, tblLine.lineName
HAVING (tblLine.lineName)="name of line");

criteria being name of line. Choices being line 1 or line 2

What I am looking for is one query to somehow generate the info on the two different lines in two different reports.

Is there a way or do I actually have to write the different reports?

 

[pbaldy]

It sounds like you want a simple parameter query. I'd put a combo box on a form with the choices, and a button to run the report. The query behind the report would look like:

HAVING tblLine.lineName= Forms!FormName.ComboName;

 

[beckie1234]

Unfortunately there can be no parameters that pop up. Somehow it has to run in the background. The form is being created so that there is absolutely no typing in the form. The date is chosen from a DTPicker and then the user just clicks on a button that produces a specific report.

For now I am writing a query specific to each report and setting the button so it opens that specific report. Unfortunately that will take 42 queries to produce 42 different reports and that is just the basic reports. There will be more if I decide to get more indepth.

 

[pbaldy]

Don't create 42 different queries/reports. That would be a big waste. The normal first option here would be to have the report grouped on line (query returns all lines), such that it printed the info for the first line, then broke to a new page and did the second line, and so on.

Another option if that's not practical would be to create a report that filtered on a hidden textbox, similar to what I described above. Then in code, you open a recordset that returned the desired lines. Within a loop of the lines, you'd place the value of the current line into the textbox, then run the report. That would print the report once for each line.

 

[beckie1234]

Example ??
The second choice seems more along the lines of what I need but not sure how to go about it.

These reports work along the lines of drill down.
One group has a specific set of criteria and the second group has the same plus more and the third has even more plus all the previous criteria.