If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Report Filter Question
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 |
#2
|
|||
|
|||
Report Filter Question
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... 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. 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 -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#3
|
|||
|
|||
Report Filter Question
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] |
Thread Tools | |
Display Modes | |
|
|