Date filter/parameter with 15 subforms!

[Guest]

Hi all. I am very new to access, but I'm trying to read around and learn.
I have managed to make one report that pulls data based on the date using a
parameter. However, I am now working on a report that shows all maintenance
checks on all 15 individual pieces of equipment assigned to me. However, it
does so in a massive report (I have 15 'types' of equipment, but about 100
individual records generated weekly).
To make things easier, I have created a table and sub-query for each piece
of equipment, then linked them into one huge report.
I did manage to create a column for "week start date" so the
parameter/filter doesn't need a date range. I've narrowed that down to one
day for all records entered that week.
However, I don't want to delve into having a user open a report and having
15 windows pop up demanding parameters. I want one form to pull all of the
'weekstartdate' values from all 15 tables.
I tried one massive query, but since some equipment may not be inspected in
a given week, it kept coming up blank, not to mention being 67 screens wide!!!
Is there a convenient way to do this? I need to be able to use a drop-down
menu to pull up whatever week is necessary...
((If possible, I would also like to make it only for the current fiscal
year, and have a second report available for 'archived reports', but this is
WAAAAY above my head and can be an additional feature way off in the future.))

 

[Guest]

You have a table for each piece of equipment?

Sounds like you have normalisation issues.

 

[John Spencer]

First of all it sounds like you have a table design issue. It is almost
certain that you should not have 15 tables - one per piece of equipment.
You probably should have one table for all the equipment with an identifier
field for the equipment.

However, if you have to live with your current design, then I would suggest
you add a form that contains the parameter data you need. The form will
NOT be bound to any table or query.

On the form you should have a control where you can input the parameter
value and a button that will turn your report. In each query where you
currently have the parameter, you would replace the parameter with a
reference to the form control.

Forms![NameofYourForm]![NameOfYourControl]

Once you have done that, the user opens the form, inputs the required data
in the control, presses the button and the report should run using the value
in the control for all the underlying queries. (The form must remain open so
the queries can see the control and its value)

You should seriously examine your table structure. Look up normalized
databases. One place to start
ACC2002: Database Normalization Basics (Q283878)
http://support.microsoft.com/?id=283878

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..