Complex Reporting

[jmw229]

Hey, again I grace you guys with another question.

I've run into another speed bump - REPORTING! I didn't create the database - just simply adapting it to the needs. It was created externally.

Since Access allows only a certain number of fields per report, I divided the reports into 6 separate reports. However, these are all based on one record (a program provided to clients). The database isn't normalized at all and I've asked if I could do that but he'd rather just get it up and running for the pilot run).

I want to create a form that allows the user to select the program from a drop down list and preview the combined the reports for that program. For example if I select Program A, then all 6 reports will be merged into one for that program (don't know if thats possible in Access). It should also have a preview all where you can preview all the reports for hte program the same way. They should also be able to print it! LASTLY, extract to word.. :|

I HAVE NO IDEA WHERE TO START!!!!! Someone guide me lol
I'm not sure if i should start!!

I was thinking this would involve a macro, but I don't see how a macro could do such a thing without some VBA. Then again, I don't even see Access being able to merge reports. Maybe you guys have alternate suggestions. There are approximately, 70 fields I'm reporting on all being handed as a package to over 50 program managers.

HELP! haha

[timothybard]

You are able to use subreports within a report; this is just like using a subform on a form.

Here is a link to Microsofts solution on how to export a report to MS Word:
http://office.microsoft.com/en-us/access/HA010448891033.aspx

The procedure is simply to export it as an RTF file. You should be able to do this in vba with the DoCmd.OutputTo command.

[jmw229]

Hey Tim,

You are always to the rescue..haha

I'm going to work on it tomorrow.

I did a sub-report in a separate report because it would only fit landscape. im going to try doing subreports tomorrw... Do you think that its an efficient and more sensible way to do this rather than 6 different reports ?

Thanks again!

[timothybard]

Just like subforms, there are times when it is very appropriate to use subreports. However, you may notice some performace issues when using subreports.

I usually stay away from subreports unless I need to use them. The only reason I mentioned it in this thread is because you mentioned the need to combine several reports into one report. If I had to choose between having six seperate reports vs one report with six subreports, I would choose six seperate reports.

A time I would use subreports is when there is one table that has two 'child' tables. For example, if I have one table that stores people, one table to store their addresses and a third table to store their phone numbers, then I think it would be approriate to have a main report showing the people's names and two subreports: one to list address and the other to list phone numbers.

Having said all that, I would encourage you to go ahead and create one report with six subreports and see how you like it. If it meets your needs, great! If it does not meet your needs or if you notice performance issues, then it shouldn't be too difficult to turn each subreport into it's own seperate report (each subreport is a seperate report in acess, you just need to add a heading, page numbers, etc).

[jmw229]

Hey Tim.

I've gone ahead and tried the subreports.. and ive been trying to figure out for the last 2 hours why its screwing it up so much lol... It adds blank pages in between, i tried page breaks but found they did nothing.. It also repeats a report for each record like 20 times.. :| i'm going to email it to you...

what i'd rather do, instead of subreports, is 6 separate reports and have a form that allows the user to simply just lookup the report drop down... give them the option to preview that report and then print the selected reports for that record as well as the option to print all records for those report types.

eg. Combo-box lookup of program name

"CLick the checkbox to Add sections of report:"
- Client profile
- resources
- criteria
etc.
I'm going to email you the sample.. it has my issue of weird subreports in it. :S maybe you can shed some light on what i've done wrong.

thanks