Multiple Queries Into One Report?

[andy_dyer]

Firstly, apologies as I don't know whether this fits better in queries or reports.

I have approx. 20 queries that I need to fit into 4 reports.

At the moment I am exporting the queries to Excel and have made a totals page on excel which pulls all the data together and "looks pretty" so that it can print on one page for each report.

Ideally I would like to keep this in house without the need to export to excel.

As far as I can tell so far it works out as one query = one report, can anyone give me a definitive answer so that I can either change what i have or know that I've done the best I can?

Thanks

Andy

 

[IMO]

You could create one Query which holds the 20 others and link the others within the single Query (ie ID), then select the fields from each that you need for the report. I think that should work.

IMO

 

[Pat Hartman]

Depending on how the 20 queries are related to each other, it might make sense to use a main report with several subreports.

 

[andy_dyer]

All of the queries are based on the same table.

But they are each quite complicated with 4-5 different criteria needing to be satisfied.

I could get them all onto one query before, linking them all using the ID field, but when i viewed them they didn't group correctly.

i.e. I had Male 1, Female 1, Male 1 instead of Male 2, Female 1 under the field "Sex"

Like I said I have got round this now by exporting to excel but would like a more professional touch to the database!

Thanks

Andy

 

[Pat Hartman]

You can use separate subreports, each based on a different query and put them all on the same main report.

It sounds like you didn't really need 20 queries though. If you'll post a couple of them, I'll be able to tell for sure.

 

[andy_dyer]

I have just copied the tables that are relevant and the queries for one report.

In this case I want to get all 6 queries into one report.

Please let me know if you need anymore information.

Thanks for your help on this!

Andy

 

[Pat Hartman]

For the third time: Put 6 subreports on a main report.

 

[andy_dyer]

How??

As far as I know you can only creat reports from tables & queries, how are you able to link reports together creating sub-reports and a main report??

Sorry if this is blatently simple but I honestly have no idea!!

Thanks

Andy

 

[IMO]

Place the subforms based on each of your queries in your master report. In the properties box for each subform link the child and master fields with the ID (or however they relate to each other).

Hope this helps

IMO

 

[andy_dyer]

Nope!!

What's a sub-form??

Sorry...

Andy

 

[IMO]

Open your report in design mode and select your tool box. If you hold your mouse over the controls you'll come to one which says 'Subform/Subreport'. Select this control and mark out on the report where you'd like it to go. The wizard will guide you through on which table or query to select.

IMO

 

[MsLady]

Gbam!

Sometimes just reading around this board, it's amazing the things you learn.
I ran into this problem some time last week and after spending hours fooling around access and looking for a way to join my multi-queries on one report and trying a billion different things. I just thought I might have heard something called subreport/subform before, so i tried it for the first time. I wasn't even sure if that was the right way, but it worked. I went around a happy person that day . I wish i had that knowledge or better yet, did a serach here. I wouldn't have spent hours being miserable in access

just ran into this thread...