Dynamically create a report based on selected criteria

[shawnrye1980]

I have a table that has 15 managers and I have queries that I need for there employees I have 5 union all queries because there are too many where clauses, I have done this and based off the main union all query I created a report for each manager this reuires 15 separate reports 1 for each manager listing all of there employees who fit the criteria for the where clauses, can I dynamically create 1 generic report and select the manager from a dropdown list and dynamically get back all employees into the report based on the manager selected instead of having 15 queries and 15 reports??

[zmbd]

Wow...
Yes you should be able to do this, the ease of implimenting it will depend on your database design.

It might really help if you will post your SQL.
Please do so by first clicking on the [CODE/] button first and then pasting the SQL between the [code] tags. Please place each query in its own code block... add a title so that we can follow.

Also haveing the Version of Access your are using and your table design and relationships would be helpful. Something like:
Access2010
tbl_name1:[field1_name](Key, datatype, relatedto); [Field2](properties), [Field3]...
tbl_name2:[field1_name](Key, datatype, relatedto); [Field2](1:M w/tbl_name1, numeric-long, other properties), [Field3]...

at this point we do not need your database itself

[jimatqsi]

Note that in VBA code you could launch a report like this

DoCmd.OpenReport ReportName, acViewNormal, Filtername, WhereCondition

Provide the report name and a where condition (such as "Manager='Tom'" )

DoCmd.OpenReport "EmpListByManager", acViewNormal, , "Manager='tom'"

Put code like that in the click event of the button that will launch the report.

Jim

[zmbd]

jimatqsi:
Would it not be better to understand what OP currently has in place before we start tossing code at the issue?

[jimatqsi]

I'm not "tossing code at the issue" I'm giving the original poster the fundamental information that may enable him to solve the problem himself. There's no need for us to see and understand his code if all he/she needs is a little basic info. If I missed the mark, I'm sure the OP will let us know. No need to make it more complicated than need be.

[zmbd]

And yet OP has a drop down list in the form
If we can use that information to feed criteria to the generated report we can make the code easier to write... it may be that we simply need to alter the SQL to use the form's control or maybe we need to alter the underlying VBA... and so forth... without the information I requested, we may inadvertantly send OP down the wrong path and worse yet, we must blindly follow them down this path. :(