Combine two reports without using a subreport

[Stephenson]

Hello all,



Thanks for having a look.

I have two reports, JobReport and ProductReport that I would like to combine into one report. I've made several attempts with my reports named Attempt. I've tried to put ProductReport on JobReport as a sub, but it crashes my computer. Please take a look at my attachment and see what I'm working with.

My users will access/filter it thorough my form ReportSearch. Currently I'm using this to call JobReport.

I look forward to any suggestions you may have as I'm new to using reports.

Thank you for looking

[Gina Maylone]

Hi! I'm just curious, what do you have against using a sub-report?

Gina

[Stephenson]

Hello Gina.

I'm not necessarily against using a sub-report. I've just not been able to do it without access crashing on me. Something must be wrong with my database that is causing this. I just have no clue what that may be. I have also been warned that they can cause a report to be slow to load. Perhaps I have been misled.

[Gina Maylone]

It's possible you have some corruption going on. Create a new database and import all of your objects into it, repair and compact. Then try the sub report again. Personally I think all of the lookups you have in the Attempt report would slow your db down more than using a subreport.

HTH
Gina

[Stephenson]

I think you're right about my look ups. I'd be interested in hearing any suggestions to get similar results with out having them.

I will create a new DB and import my current work. Thank you for the suggestion.

[HiTechCoach]

I think you're right about my look ups. I'd be interested in hearing any suggestions to get similar results with out having them.

Assuming you have a well designed (normalized) database ...

All the data can easily be gathered in the report's record source (query).

I have NEVER used a combo box or list box on a report.

Sub Reports as what make Access Reports so powerful

I do use lots of sub reports. It is rare that I have a report that does not have at least one sub report.

TIP: Be very careful about using Report View mode. I recommend using Print Preview mode.

This may help: Sub-Forms and Sub-Reports (Click Here)

About having a well designed (normalized) database ...

Took a peek at your database.

I did not see any attempt to use a sub report. I could not find a main report and the two sub reports.

The report ProductReport sure has a lot of =IIF() in the control sources. That is a huge red flag that are design issues. I have never had to work that hard at accounting system that were much more complex that what you have so far.

I do believe all the hard work and struggles you have experienced are a result of the design of ypur tables. What you are attempting should be basic/standard stuff with a properly normalized database.

I highly recommend that you fix your normalization issue with your tables before going any further. The time you spend improving (normalizing) your design of the tables will be saved many times over in the future. If not, you have a very slim chance of things getting any easier. That comes from experience helping over 1000 people in the past 25+ years.

[Stephenson]

HiTechCoach. Thank you for your suggestions.

As for normalizing do you have any suggestions for me?

I feel there is a better way to handle my vendor table, but am unsure how. To me it seems like the vendor types should be separated, but occasionally they will provide more than one type of service. Beyond this I'm not sure what more I can do to normalize my DB.

Any suggestions would be greatly appreciated.

[HiTechCoach]

HiTechCoach. Thank you for your suggestions.

As for normalizing do you have any suggestions for me?

Yes. A field name should never contain data. You have this issue a lot in the table VendorPurchaseLog.

Example: For people you have a single field named gender. You do NOT have fields name Male and Female. You can print the gender data on a report are two check boxes, one for Male and one for Female. That is only how you would display it. Not how you store the data.

I feel there is a better way to handle my vendor table, but am unsure how. To me it seems like the vendor types should be separated,

That is Excel thinking, which is a report. An Access query can split the vendors by type when needed.