[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Using a table to run a query from and writing each item into a report.

Posted on 2011-05-04
11
Medium Priority
?
439 Views
Last Modified: 2012-05-11
I have a table with Department Numbers and names and need to create reports for each of the department numbers from the table.  The reports are created from a personnel table and it would be by department number.  I am working to automate the report creation versus someone having to select 200 separate department numbers.

Table one - TBL_Dept_List  -  DeptNo, DeptName, TotalTeam
Report has data in it along with the team member information with the DeptNo as the link.

Could use a little help  Thank you.
0
Comment
Question by:frank_guess
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 35692648
You can create a table of department numbers and do VBA module to read the table and create a report for each table value.
You can create a table of all departments and use an additional field to select departments on which to report and do VBA module to read the table and create a report for each table value.
Both of the above would generate a different report for each department or
You can create a table as above and use it in a query to create your reports in one file.
0
 
LVL 12

Expert Comment

by:danishani
ID: 35692669
0
 

Author Comment

by:frank_guess
ID: 35693223
Thank you
The problem is I have the table already finished but I am looking for the module or code behind the button to make this happen.  

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 12

Expert Comment

by:danishani
ID: 35693305
Did you try to create a Report based on a Group/ Section of your DepartmentName / No?
Then follow the steps as suggested in my link.

HTH,
Daniel
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35695497
If I am reading you request correctly there is no need to create multiple reports from data in the same table.

Simply create one report with all the data, then simply filter it for the data (department, team member, ...etc) you wish to display.

I believe this is basically what danishani is suggesting, so I'll step aside and let him outline the principles...

;-)

JeffCoachman
0
 

Author Comment

by:frank_guess
ID: 35750846
Sorry I guess I was not clear.  I have a table with the business cost centers, I need to run a report for each cost center number and save the report as a file with that name example:

Cost_Center_680_5040_05_12_2011_PersonnelRpt.rtf
Cost_Center_680_5022_05_12_2011_PersonnelRpt.rtf
Cost_Center_680_5075_05_12_2011_PersonnelRpt.rtf

These reports go on through over 200 or more business centers.  The table is a list of those cost centers and the reports are created by matching the cost center number against the Cost center number that is listed in the data file that is used to create the report.  Even through the report can be page separated by start the report by the cost center number this does not allow you to create individual reports.  Why am I needing this is to create a folder or directory of individual cost center reports without having to manually run each one and save it myself.  Takes all day that way versus using the table and creating each report without human touch.
0
 
LVL 12

Expert Comment

by:danishani
ID: 35751639
Ok, below link give you an idea on how to do this:

http://bytes.com/topic/access/answers/799273-access-2007-create-report-per-group

Just copy the idea and replace the code to your convenience.

HTH,
Daniel
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 35759218
frank_guess,

Try this sample file:

db352.accdb
0
 

Author Comment

by:frank_guess
ID: 35769021
I will review and get back to you.  Thanks
0
 

Author Closing Comment

by:frank_guess
ID: 35769068
That is exactly what I am looking for.  Thanks
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35771971
;-)
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question