Running two queries from a single input parameter

[MLangendorf]

I am hoping to gain some insight here. Please help!

I have a report based on a single query (which is working just fine). However, the query outcome is a single line of data most of which is crunching numbers.

The numbers represent aggregate data from multiple persons which represent multiple agencies.
What I would like to do is have the multiple agencies listed at the top of the report so that the reader can see what all agencies are making up this aggregate data.

I have developed a separate query that generates the agencies based on the same input parameter as the query that generates the aggregate data.


How can I link these so that all I need to do is enter the input parameter (date) one time and flip the switch on both of these queries.

Since both queries have a common field (the date), I tried combining them but ended up with some sort of circular reference error that shut me down.

I tried using a subreport but I still ended up having to enter the input parameter twice (sometimes 3 times). I eventually gave me what I wanted but I don't want to have to enter the date multiple times.

thanks to all,

Mark

[pbaldy]

Most of us would use a form to gather the user input. Your queries would get the criteria from there, and thus you would avoid multiple prompts.

[MLangendorf]

Thanks Paul,
But Im not sure how to develop the form that would trigger queries.
any advice?

[pbaldy]

At the simplest, a form with a textbox for the date and a button to open the report.

[MLangendorf]

Thank you Paul,
I didn't realize that I had used this same solution (and actually on the same form) for a different problem.
It works great actually.
Thanks a million.

[MLangendorf]

Since solving this thread. I would like to briefly describe the process of using an input on a form to run multiple queries for other 'very amateur' db prgrammers like myself.

I had two queries that were being used for a single report. They ran based on a single date input parameter. I used one query to create a subreport.

Every time I would try to run the report I had to enter the date criteria twice (once for each query that was being used to form the report).

The simple solution was to create a simple text box (I put mine right next to the button that I already had to run the report) format the text box to 'short date', and then go to each query that was asking for that short date and enter the name of that text box into the criteria cell (under 'date') in the query design.

Going back to the form to run the report, I simply entered the date that I wanted, click on my run report button and both queries would run from that single date-formatted text box.
Voila'! Problem solved!
This is a fantastic tool for the toolbox.

[pbaldy]

Happy to help!