A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Report Filter Question



 
 
Thread Tools Display Modes
  #1  
Old August 26th, 2009, 01:15 AM posted to microsoft.public.access.gettingstarted
Lostguy
external usenet poster
 
Posts: 73
Default 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  
Old August 26th, 2009, 01:39 AM posted to microsoft.public.access.gettingstarted
fredg
external usenet poster
 
Posts: 4,386
Default 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  
Old August 26th, 2009, 01:46 AM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:43 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.