How Do I Create Many Individual Reports Simultaneously?

bcmarshall

Registered User.
Local time
Yesterday, 20:49
Joined
Jul 17, 2010
Messages
92
I want to create dozens of reports simultaneously in Access 2007, one for each salesman, by setting the date parameters. So, for May 1-May 31, I enter the dates and Salesman1's report is created with only his data, and all other sales reps get their own indivudual report containing only their data generated at the same time with the same click. I have no problem creating it individually, putting in Salesman1's name, for example.

What I'm trying to do is to create a one-click routine that will generate the dozens of reports for that date range simultaneously, each sales rep on his own page, and then print the entire pile with one print command. Grouping doesn't help as it prints it all on the same page. I want to be able to give each salesman his own individual report containing only his data and totals.

I'm suspecting the problem is somewhere in the query feeding the report. I have created a select query which outputs all the appropriate fields, but the problem is that the report doesn't stop for each salesman, and then start anew for the next one. It just runs them all together into one long report.

Does anyone have a suggestion on how to do that?
 
Grouping is the correct way to go. Whatever you want in the header of the report, you put in the salesman header. Whatever you want in the footer of the report you put in the salesman footer. Then, at the very bottom of the salesman footer you put a page break which will cause a new salesman's data to print on its own page.
 
Then, at the very bottom of the salesman footer you put a page break which will cause a new salesman's data to print on its own page.
I wouldn't use the page break item though as it is simple enough to do without using it. Just go to the group footer and set the FORCE NEW PAGE property to AFTER SECTION.
 
One other thing you can do is to use a recordset to pull each salesman and then open the report to create it for them only:
Code:
Dim strSQL As String
Dim rst As DAO.Recordset
 
strSQL = "SELECT DISTINCT [SalesmanIDFieldNameHere] FROM TableWithSalesmanInfoHere ORDER BY [SalesmanNameField]"
 
Set rst = CurrentDb.OpenRecordset(strSQL)
 
With rst
  Do Until .EOF
      DoCmd.OpenReport "ReportNameHere", acViewNormal, , "[SalesmanID] = " & !SalesmanIDFieldName
      .MoveNext
  Loop
  .Close
End With
 
Set rst = Nothing

and if you do something like that it will print out the report (including report header and footer) for each salesman.
 
Thanks for the help, Bob. The firest posted solution sounds like that will solve my problem, but I can't tell yet. I hate to sound stupid here, but the group header shows but not the group footer. How do I make the group footer visible?

The second solution you posted won't help me. I want to run all the sales reports for each salesman with one click. There are too many and it's too time consuming to select each one and run his/her report individually.
 
Thanks for the help, Bob. The firest posted solution sounds like that will solve my problem, but I can't tell yet. I hate to sound stupid here, but the group header shows but not the group footer. How do I make the group footer visible?
When the report is open in design view, down near the bottom you will see various group options.

See this screenshot which shows that area (this was created for someone else's problem so it doesn't mirror yours at all but it is where you would make the selection of WITH A FOOTER SECTION.

attachment.php



The second solution you posted won't help me. I want to run all the sales reports for each salesman with one click. There are too many and it's too time consuming to select each one and run his/her report individually.
Umm, you apparently didn't understand. The code I provided you will RUN ALL SALES REPORTS with a SINGLE CLICK of the button. It iterates through ITSELF and prints each salesman report separately so you don't have to.
 

Attachments

  • groupbyqtr.png
    groupbyqtr.png
    6.6 KB · Views: 2,028
AWESOME! It worked perfectly. Thanks so much. I was goin' nuts looking for the combination to that safe!
 
Dear Bob,

I need your help!

The solution you provided to this particular issue is on the right track to what I am needing, but my brain is having trouble wrapping itself around some of the concepts necessary to achieving this.

I tried the example you gave. While it printed the appropriate number of reports, it included information for all of my employees instead of an individualized report for each employee.

And what I really want to do is create an individualized query for each employee, extract the info into an e-mail template, and e-mail personal info to each employee their. But the overall goal is essentially the same I want to extract info pertaining to each employee from one pool of employee data and create something individualized for each employee.

Any help appreciated! I can provide more expicit details, but no db sample. Protected from e-mailing our db & the like.


One other thing you can do is to use a recordset to pull each salesman and then open the report to create it for them only:
Code:
Dim strSQL As String
Dim rst As DAO.Recordset
 
strSQL = "SELECT DISTINCT [SalesmanIDFieldNameHere] FROM TableWithSalesmanInfoHere ORDER BY [SalesmanNameField]"
 
Set rst = CurrentDb.OpenRecordset(strSQL)
 
With rst
  Do Until .EOF
      DoCmd.OpenReport "ReportNameHere", acViewNormal, , "[SalesmanID] = " & !SalesmanIDFieldName
      .MoveNext
  Loop
  .Close
End With
 
Set rst = Nothing

and if you do something like that it will print out the report (including report header and footer) for each salesman.
 
Dear Bob,

I need your help!

The solution you provided to this particular issue is on the right track to what I am needing, but my brain is having trouble wrapping itself around some of the concepts necessary to achieving this.

I tried the example you gave. While it printed the appropriate number of reports, it included information for all of my employees instead of an individualized report for each employee.

And what I really want to do is create an individualized query for each employee, extract the info into an e-mail template, and e-mail personal info to each employee their. But the overall goal is essentially the same I want to extract info pertaining to each employee from one pool of employee data and create something individualized for each employee.

Any help appreciated! I can provide more expicit details, but no db sample. Protected from e-mailing our db & the like.

Can you provide the exact code you are trying to use? Also, what is the record source for the report? Please provide the SQL for that as well.
 
Bob's solution to me worked perfectly. It wasn't code. It was simply "I wouldn't use the page break item though as it is simple enough to do without using it. Just go to the group footer and set the FORCE NEW PAGE property to AFTER SECTION".

The query the report is based on is already Group By [Rep]. It's all that I needed to print individual reports for each salesman.
 

Users who are viewing this thread

Back
Top Bottom