Report based on multiple queries

[Ivan R via AccessMonster.com]

I have a report which is going to extract information from several tables.
At the present time, I have combined the information via subreports. There
is approximately 12 subreports on this main report. I have read that this is
the best way to do this, however even 12 subreports is an extremely laborious
task to setup. I currently do have it working, but the ideal would be to be
able to run the queries and return only the information needed to the text
boxes.

My database has approximately 15 tables. There is a main table that contains
the really important informatioin like case number and type of case that is
being handled. The other tables contain information like contact information
more information based on the type of case that it is, employee handling the
case, etc...

I have setup multiple queries that calculate the number of cases that each
employee has, the types of cases, the amount of money, property, or assests
being handled. Because I cannot combine these queries I have had to setup 12
subreports to return this information even though it probably could be
handled with one query that does the different calculations in code. So, my
main question is can I set up one query that in code allows me to perform
different calculations and return the number to a specific field on my report
and get rid of the multiple subreports/queries?

 

[Pat Hartman\(MVP\)]

You can try joining the totals queries to the main query. As long as the
totals queries return only one row per main table row, everything will be
fine. Use Left Joins just to be safe.

 

[Guest]

Without seeing your data structure, it doesn't make sense as to why you would
need 12 Subreports(I used 4 once), especially when you only have 15 tables
total. Maybe post the structures of some of the child tables??

With Access, there is always a different way, but it doesn't always mean a
better way.

Steve Clark, Access MVP
http://fmsinc.com/consulting