MS Access Report/Subreport Export

Good day,

I have a spreadsheet contaning about 50K sales records, grouped by VP,  Sales manager, and Sales rep.  I would like to create a report process in MS access that  will allow me to
group the output by VP
place  the output  for  each Sales manager on a separate tab/subreport (think excel workbook tabs)
email the report to each VP for his /her respective sales manager team

I know how to write the queries to break out the sales teams; what is the best way to create the report--export excel build report/subreports?

Please advise.

Thank you!

wasmithpfs
Wm Allen SmithAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jadedataMS Access Systems CreatorCommented:
Steps:
1.  Import the records into a temporary table for handling as data, or link to the spreadsheet as a table (not all the same table rules will apply as a linked table though.)

2. Establish a logic chain or loop through the VP identities, probably with a control table or separate query (as these change over time)

3.  Go find and implement VBSendMail.dll in your project.  This is the most simply powerful and easy to use add-in for vba since this.  (Kudos to Dean Dusenbury)

Your application will

do until rsVPNames.eof
    Write the data to a external sheet in a known location, with a logically developed name.
   use VBSendmail to build the mail package
   Attached the xlsx to the email
   copy yourself on the item of course
   log that you sent the item for your records
   rsVPName.movenext
loop
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<jadedata>
Long time no see :). Lots of changes here on EE!
</jadedata>
pjg418Commented:
I can see two ways to accomplish this, but the main problem i see for you is that you cannot have interactive datatabs on emailed/printed access reports

1.
Import Data from spreadsheet into access table

Create a form, from that form I would would get a record set of the sales managers and VPs, and their email addresses.

then create a single report that would report data by Sales Manager.

supply a filter to your report to narrow it down to a single sales manager.

Then from your form use a for each on your data set.
Run the report and supply the sales manager filter.
Email the report to the VP and Sales Manager as PDF.

As far as i know there is no way to have multiple tabs on an access report that is printed / emailed... so the VP would get one email per sales manager

2.
Import Data from spreadsheet into access table

use a form get a dataset of the VP, and sales managers w/ email address and a second data set of the VP with their email address.



Create an access report that lists sales by sales manager

Use a nested for each statement on your two data sets.

For i = 0 to datasetVP.rows.count - 1
       for j = 0 to datasetSM.rows.count -1
                    if DataSetVP.row(i).item("VP NAME") = datasetSM.row(j).item("VPNAME")
                                 add work here
                     end if
       next
next

Use quires and create an excel spread sheet for each VP and add work book tabs for each Sales Manager and send that to each VP.


Run your access report and supply a filter for the sales manager and then email that access report to them as a pdf or single page excel document to the sales manager. (NOTE: If you want to email as excel to the sales manager then you would need to run split your for statements and do one for the VP's and then do your work for the sales managers. As I Don't think you can create two separate workbooks at the same time.)

With this second option the VP gets all the data in one place, and with one email, instead of multiple emails.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jadedataMS Access Systems CreatorCommented:
yep changes noted... "Looking Good!!"
Wm Allen SmithAuthor Commented:
Thanks for the feedback! :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.