One Report From Several Queries? 1

[SimRick]

I want to create a report that shows the result of 5 different parameter queries, each from a different table. The parameter is the same (last name, first name)in each query. How can I do that without entering the parameter 5 times? Thanks

 

[scriverb]

Just name the prompt the same for all five queries:

[Enter LastName,FirstName:]

You will only have to enter the parameter one time and all five queries will use it. Now a better way to do this is to prompt only for one of the queries and link to the other queries with an inner join. Once the record in the first is found the inner join will bring in all of the other matching records from the other four queries.

Do you understand this process? Get back if you don't and I will try to be more specific. Give me a little more info on your queries and tables and i can help with the joining process.

Bob Scriver

 

[SimRick]

Thanks for the help. I have had trouble with the join. It is an employee database. There is an Employee table with the name [last, first] as the primary key. There is a Training table with fields Name, Type and Date (among others), a Requirements table with Name and other fields, and an Information table with Name and other fields. I have enforced referential integrity in the name fields for conformity. I want to generate a status report that would allow me to enter the name as a parameter and display information about that employee from all the tables. I tried joining all the name fields and using only one query, but I get an impossible number of results - it shows all combinations of data from all tables. I fear that I am in a little over my head!

 

[scriverb]

The best thing to do here is create a report that has a Record Source of a query listing all the fields for the table with a Criteria prompt for the Name as we discussed above. You can display all of the Employee identification information in the Detail Section and then in that section have 4 SubReport objects. Each of these would have a Source Object form that would list the data fields of that particular table. The Record Source for each of these subreport forms would be a query selecting all of the necessary fields for the SubForm. Using the properties Link Child field and Link Master field you can select only those records that relate to the Employee master record displayed in the report.

So basicially, without having to perform any joins you create a report displaying data from Employee table and display the related data from the four other tables through subreport objects. Each of these are set to grow in size depending upon the number of records that match the employee record.

I hope that I have not confused you too much but it is a little difficult to describe this process. The easiest way is to just make a very simple report as I described with just a couple of fields and make one subreport object and an underlying subform and get the related records to display. After have done that you can move on to your bigger report.

Get back with me with any additional questions that you may have.

Bob Scriver