Report Filter Question

[Lostguy]

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

 

[fredg]

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...

This is OK.
Create an unbound form with your Combo Box control on it.
Set the rowsource of the combo box to display all the department
names.
"Select tblDepartment.[DepartmentName] from tblDepartment Order By
[DepartmentName]"

Add a command button.
Code the Command Button's Click event:

DoCmd.OpenReport "EmployeeReport", acPreview, , "[DepartmentName] = '"
& Me.[ComboName] & "'"
DoCmd.Close acForm, Me.Name

Name this form "frmDeptSelect".

Code the Switchboard to open the frmDeptSelect.
DoCmd.OpenForm "frmDeptSelect"

When ready to run the report, from the switchboard open the
frmDeptSelect. Find the correct department in the combo box. Select
it.
Click on the Command button and the report will run, filtered, and the
form will close.

I currently have one query with 20 reports and that seems crazy.

If you wish to have the ability to print "All" the departments as well
as filtered, code the frmDeptSelect Command Button Click event
(instead of as above):

If IsNull(Me.[ComboName]) then
DoCmd.OpenReport "EmployeeReport", acPreview
Else
DoCmd.OpenReport "EmployeeReport", acPreview, , "[DepartmentName] =
'" & Me.[ComboName] & "'"
End If
DoCmd.Close acForm, Me.Name

Click the command button without selecting any department and all the
departments will be shown.

 

[John W. Vinson]

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!