473,320 Members | 2,052 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Dynamically create a report based on selected criteria

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??
Jul 24 '13 #1
5 5278
zmbd
5,501 Expert Mod 4TB
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
Jul 24 '13 #2
jimatqsi
1,271 Expert 1GB
Note that in VBA code you could launch a report like this
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport ReportName, acViewNormal, Filtername, WhereCondition
  2.  
Provide the report name and a where condition (such as "Manager='Tom'")
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "EmpListByManager", acViewNormal, , "Manager='tom'"
  2.  
Put code like that in the click event of the button that will launch the report.

Jim
Jul 30 '13 #3
zmbd
5,501 Expert Mod 4TB
jimatqsi:
Would it not be better to understand what OP currently has in place before we start tossing code at the issue?
Jul 30 '13 #4
jimatqsi
1,271 Expert 1GB
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.
Jul 31 '13 #5
zmbd
5,501 Expert Mod 4TB
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. :(
Jul 31 '13 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Andrew | last post by:
How do I set up a report that runs after the user selects a month, (from a combo or list box on a form) so that the only data in the report is data from the month selected? I'm not too good with...
0
by: Richard Hollenbeck | last post by:
I have a crosstab query that shows all the scores of all the activities of all the students in all courses, with the students being in the rows and the activities being in the columns and the...
7
by: pmclinn | last post by:
I was wondering if it is possible to dynamically create a structure. Something like this: public sub main sql = "Select Col1, Col2 from Table a" dim al as new arraylist al =...
1
by: davidbennett | last post by:
Hello, I am new to MS Access. I have created a series of forms that users access to perform data entry. I now need to build in reporting functionality. I would like to create a form that has a...
1
by: gregoryenelson | last post by:
Hi all. I have created a Report with a graph also on it. The users, working through a form only (not the DB window) will want to view that Report run numbers of times against different criteria,...
1
by: fleece | last post by:
I have a continuous form based on some selected criteria. Now I want to send the search results to a report. How could I do that? It's better if I could sort the results before export to report. ...
2
by: devindersingh1984 | last post by:
hello sir i am making a project in ms access it is having two tables that is interrelated with primary key. i want to get report of current record means report should take data from both tables....
0
by: ChadK | last post by:
I am trying to open a report based on what the user selects on a form. Each individual criteria works but when I try to combine to pass multiple criteria it doesn't. I have read what I can find on...
37
by: sarega | last post by:
Hi, Here I have an array returned that is returned in responseText from the server. Based on the output that is returned by the server I have to dynamically create a checkbox. function...
11
by: woodey2002 | last post by:
This problem is driving me crazy. Hello there, i am trying to create a search form for records in my access database. The search form will contain text boxes and a multi select list box. The user...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.