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

Building a Report of Multiple Queries

P: 2
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.
Mar 20 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,366
Make a report for each query. Then make a master report and use subreports.
Mar 20 '07 #2

P: 2
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.
Mar 22 '07 #3

Rabbit
Expert Mod 10K+
P: 12,366
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
Expand|Select|Wrap|Line Numbers
  1. #[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.
Mar 23 '07 #4

Post your reply

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