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.
7 4711
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!
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
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
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
That worked perfectly! Thank you for the help.
That worked perfectly! Thank you for the help.
Happy to help.
J
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?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Rick Caborn |
last post by:
Does anyone know of a way to execute sql code from a dynamically built
text field?
Before beginning, let me state that I know this db architecture is
built solely for frustration and I hope to...
|
by: BlackFireNova |
last post by:
Using Access 2002
I am writing a report which draws data from several different tables. I
can't link all the tables in a query, as some can not be related without
truncating the data. I plan...
|
by: Steve |
last post by:
I am using Access 2000 and I have a DB that is currently running four
different queries from some Forms and subforms. These queries each
have a date field that are seperate from each other that are...
|
by: NOSPAMrclark |
last post by:
I'm wondering . . . .
What is the generally accepted method for calling reports from multiple
locations?
Example:
REPORT-A is called by selecting a record in a listbox and clicking a...
|
by: dskillingstad |
last post by:
I've been struggling with this problem for some time and have tried
multiple solutions with no luck.
Let me start with, I'm a novice at Access and I'm not looking for
someones help to design my...
|
by: google |
last post by:
I am creating a new database for use within our company, that I'd like
to make reasonably secure (short of a true server based solution). The
back-end of a non-server based database seems to be...
|
by: TonyJH |
last post by:
Hello,
I have a database in Access2003. I have set up several queries that use the parameter entry . Each query produces different outputs from various tables that go into a report. Sometimes, a...
|
by: wsox66 |
last post by:
I am new to Access and need some help building a report. I have looked through previous posts on reports but none of them seem to answer my question completely.
I am using Access 2003 and I have...
|
by: Brett |
last post by:
Hello,
Is it possible to have just one criteria and have it apply to a group
of queries?
I am trying to create a report with the separate results of 4 queries
based on a prompt for the user...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| | |