Help with queries

[kfboren]

I am running Access 2007, OS Vista.
I am wondering if someone can help me. I have created 10 tables, 10 queries for the tables and 10 reports from the quearies. The creteria for the queries is the field named "month". I have created one main report, which includes all 10 of the subreports. When opening the main report I have to enter the creteria month which is the same for all 10 queries. How can I open this main report and only enter the creteria one time which would satisfy all 10 queries?
--------------------------------------------------------------------------------

[Stewart Ross]

I have created 10 tables, 10 queries for the tables and 10 reports from the quearies. The creteria for the queries is the field named "month". I have created one main report, which includes all 10 of the subreports. When opening the main report I have to enter the creteria month which is the same for all 10 queries. How can I open this main report and only enter the creteria one time which would satisfy all 10 queries?

Hi kfboren. Subreports are most often linked to a main report via one or more common fields. If you are applying the same criteria to the month field in all your queries, why not remove the month criteria from the subreport queries and link the subreports to the main report via the month field? The subreports will then automatically follow the month value from the main report. The subreport properties will allow you to check the setting of master and child link fields. I use A2003 and not 2007, so I cannot be sure what changes there may be (if any) to these settings in the newer version of Access.

Without knowing more about the design and field structure of your tables it is difficult to be more specific. Have you normalised the design of your tables? There is a good article in the HowTo section of the forum on this topic, at http://www.thescripts.com/forum/thread585228.html.

I mention this because it is unusual to use 10 subreports within a main report, where if I read your question correctly it is the subreports that are doing the work. This tends to suggest to me that either the overall table design is not yet optimal, or you are not yet familiar with how to design queries to link multiple Access tables for reporting purposes. Perhaps you can provide more details of the structure of your tables, and what the main/subreports are setup to do?

Regards

Stewart