Building a Report of Multiple Queries

[wsox66]

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 a database with one table. I have built ten different queries to organize run calculations on the records in the table. The problem is that the queries don't all access the same columns nor do they all return the same number of rows and columns in their results. This would seem to prevent me from using the suggestions on UNIONS that I have found on this site. I also need to make the report dynamic by allowing the user to enter date ranges.

What I have so far is the ten queries and I have build an unbound form, based on a suggestion from this site, with start date and end date text boxes. I have also built a macro that opens each of the queries. The form event “On Click” call the macro which they runs each query. This results in a datasheet for each query popping up on the screen.

What I am trying to do is populate the report with the results of each query. I have tried building the report with the report wizard and selecting each query and its corresponding fields but I keep getting the following error message:

“You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, choosing fields from only the table or only the query.”

I am lost and would appreciate any help given.

Thanks.

[Rabbit]

Make a report for each query. Then make a master report and use subreports.

[wsox66]

Make a report for each query. Then make a master report and use subreports.

Thanks, Rabbit.

I was able to build a report and and use the queries as sub-reports.

However, I still need to link my form to the main report. I want to enter the date range in the form and have it create the report which has the data only for that date range. How do I do that??

Thanks.

[Rabbit]

Thanks, Rabbit.

I was able to build a report and and use the queries as sub-reports.

However, I still need to link my form to the main report. I want to enter the date range in the form and have it create the report which has the data only for that date range. How do I do that??

Thanks.

You wouldn't. The form will hold the date range. A query will reference the controls on the form. And a report will be based on the query.

You reference the controls on the form from the query using

#[Forms]![Form Name]![[/i]Control Name[/i]]#

The # are to delimit that it's a date. So you'd set up the criteria to filter for the dates you want in the query and base the report on that query.