By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,384 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Multiple Queries Run for Report, want to only require one user input

P: 9
I am creating a report that outputs the contact information and details about a water treatment plant, and needs to include information about people who work there.

If I tie all the information to a single query the report contains a full set of information for the plant for each of the people who work there. If I embed multiple queries into the report, the user has to input the search criteria once for each of the queries that is being run, which is kind of a hassle.

How can I tie the queries to one user input without having one large query that returns too much information?

I have tried changing the relationships within the queries and forms I am using to filter the data but cannot seem to get a solution.

Thank you.
May 7 '07 #1
Share this Question
Share on Google+
7 Replies


missinglinq
Expert 2.5K+
P: 3,532
I'm confused by the statement " How can I tie the queries to one user input without having one large query that returns too much information?"

The query will only return the information you ask it to! If, for example, you want a list of the employees at the particular plant with their contact phone numers, but not their addresses, you simply include their names and phone numbers, but not their addresses!
May 7 '07 #2

JConsulting
Expert 100+
P: 603
I am creating a report that outputs the contact information and details about a water treatment plant, and needs to include information about people who work there.

If I tie all the information to a single query the report contains a full set of information for the plant for each of the people who work there. If I embed multiple queries into the report, the user has to input the search criteria once for each of the queries that is being run, which is kind of a hassle.

How can I tie the queries to one user input without having one large query that returns too much information?

I have tried changing the relationships within the queries and forms I am using to filter the data but cannot seem to get a solution.

Thank you.

Generally...you would allow your user to either select or enter a parameter for your query/queries on a form then launch your report from there. The number of times it gets accessed via your queries is not relevent at that point.

It's not clear what you "plan" to do with your report, whether you're creating one large recordset, or creating small ones using sub-reports...so it's very hard to answer your question and still sound intelligent.
J
May 8 '07 #3

P: 9
I have it set up so that the user can select the search criteria for the query on a form.

I have four queries that need to be run to gather all the information for the report.
The first query gets a street address from one table. The second one gets the names of who is running the plant from a second table. The third one gets the names of employees at the plant from another table, and the fourth gets the plant's answers to a questionnaire from the last table.

The report outputs the proper amount information if there is only one employee, and the report is four pages.

If there is more than one employee at the plant, the report is (Number of Employees)*4 pages in length, as the address of the plant, name of the manager and questionnaire answers are all put into the report once for each employee, providing a lot od unnecessary duplicate information.

The employees names are in a sub form within the report set to the default view of Datasheet.

The report outputs one set of data for the plant if I keep the user input as a parameter input value in the query, although that causes the user to have to input the search criteria four times (once for each query) in a dialog box once the report is opened.

My question is how can I limit the data in the report to one set of addresses, manager and questionnaire if there is more than one employee name in the database, instead of one set being output for each employee, as it is all just duplicate information?

Thank you for your help
May 8 '07 #4

JConsulting
Expert 100+
P: 603
I have it set up so that the user can select the search criteria for the query on a form.

I have four queries that need to be run to gather all the information for the report.
The first query gets a street address from one table. The second one gets the names of who is running the plant from a second table. The third one gets the names of employees at the plant from another table, and the fourth gets the plant's answers to a questionnaire from the last table.

The report outputs the proper amount information if there is only one employee, and the report is four pages.

If there is more than one employee at the plant, the report is (Number of Employees)*4 pages in length, as the address of the plant, name of the manager and questionnaire answers are all put into the report once for each employee, providing a lot od unnecessary duplicate information.

The employees names are in a sub form within the report set to the default view of Datasheet.

The report outputs one set of data for the plant if I keep the user input as a parameter input value in the query, although that causes the user to have to input the search criteria four times (once for each query) in a dialog box once the report is opened.

My question is how can I limit the data in the report to one set of addresses, manager and questionnaire if there is more than one employee name in the database, instead of one set being output for each employee, as it is all just duplicate information?

Thank you for your help

You need to set up grouping in your report.

If the addresses, manager and questionnaire are all the same. Create a group for addresses, then drag the manager and questionnaire boxes into the same group section. The Employee data should remain in the detail section of your report. This will list all employees for the given group.

J
May 8 '07 #5

P: 9
That worked perfectly! Thank you for the help.
May 8 '07 #6

JConsulting
Expert 100+
P: 603
That worked perfectly! Thank you for the help.
Happy to help.
J
May 8 '07 #7

P: n/a
I am trying to create a report from two queries. one queries to display the personal details for one parent selected through a input prompt of a file number field(key number). The second queries is to display the list of selected parent's children at the same report.

Each child record in the children table has a file number field (same as file number from parent table).

I would like the report to display on single user input

How can i combine the output of both querries from one user input?
Oct 15 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.