On Tue, 25 Aug 2009 17:15:45 -0700 (PDT), Lostguy wrote:

Hello!

I have tblEmployee with 400 employees and a tblDepartment with 20
departments.

What is the best way to set it up so that I can print a report of ALL
employees and their associated department (no filter) and then have
the user select (via a dropdown) the department that they want to see
a report on.

I was thinking that a button on my Report Switchboard form opens
another form with just a combobox tied to tblDepartment, and then that
combobox supplies the filter for the report...

I currently have one query with 20 reports and that seems crazy. And
20 queries with one report doesn't seem right either. So there has to
be a way to do one query, one report, and then some intermediate user
thing.

??
VR/Lost

One *PARAMETER* query with one report!

Create a little unbound form named frmCrit, with one combo box, cboDept,
allowing the user to select a single department.

Base the report on a Query on the employee table joined to the department
table (just to pick up the department name and any other fields that are only
in that table).

On the criteria for the DepartmentID in the employee table put

=[Forms]![frmCrit]![cboDept] OR [Forms]![frmCrit]![cboDept] IS NULL

This will let the user select a department, or show all employees if the user
leaves the combo blank.

Put a command button on frmCrit to open the report (you can use the command
button wizard to do this).


Parameter queries are absolutely *essential* to any productive use of Access -
get to know them!

--

John W. Vinson [MVP]