sub reports - grouped 1

[skaurd]

Hi

I have a huge query, with too many fields for it all to go into one report.

Therefore I have had to split up data into many queries and thus many reports.

EBITA-margin report and query
PBT-margin report and query
Net return on sales report and query
DSO report and query
Inventory Turnover report and query
NWC growth report and query
Net Debt/Equity report and query
RoE (annualized)report and query


I want to create one report though, as I want the layout which only a report can help me with.

Is there anyway? That I can sort the layout and join all the sub reports, so that the name and the the data comes out like so;

Name of record (is in each query and report)
EBITA-margin data
PBT-margin data
Net return on sales data
DSO report data
Inventory Turnover data
NWC growth report data
Net Debt/Equity data
RoE (annualized)data
then the next record and all of the above.....

instead of;

Name of record
EBITA-margin data
(lists all 70 records)
Name of record
PBT-margin
(lists all 70 records)
Name of record
Net return on sales data
(lists all 70 records)
Name of record
(lists all 70 records)
Name of record
DSO report data
(lists all 70 records)
Name of record
Inventory Turnover data
(lists all 70 records)
Name of record
NWC growth report data
(lists all 70 records)
Name of record
Net Debt/Equity data
(lists all 70 records)
Name of record
RoE (annualized)data

I hope you know what I mean. It is repetive, no grouping is used and hence looks a complete mess.

Can anyone help?

thanks

S

 

[Sailalong]

This is the only way I know if I understand what you want. To work, all the queries you mentioned must share at least one common field. Create a master report based on one of the queries you created. In the master report under Sorting and Grouping (under View), set the Field/Expression to that common field.

Set Group Header: Yes
Set Group Footer: Yes (Header and footer help in printing)
Set Group on to: Each Value
Set Keep Together to: Whole Group

Each of of your sub reports need to be grouped in the same way but they need to have 2 Field/Expressions. The first needs to be the shared field. The second should be whatever field you based the query on, EBITA, PBT, etc. All other settings are the same.

Once you have inported the sub reports in to the master report, verify that the links are correct. For each sub report, under Properties, Data: the Link Master Fields should be the common field common to all your queries and the first Field/Expression in Sorting and Grouping of the master report. The Link Child Fields should be the second Field/Expression in the sub report Sorting and Grouping.

Hope this helps. Let me know if this makes no sense.
Sailalong

 

[skaurd]

Dear Sailalong

This is great! It worked just fine.

Can you help me further please? I cannot export the report into excel. Even though it seems like it is exporting, it goes through the process of exporting all the pages one by one, but no excel file is generated. I can export to txt file (csv) but for the purpose of making it easier for the users I wanted to export to excel.

Would you have any idea why it does not work?

thankx

S

 

[Sailalong]

Dear S:

I have never had much luck exporting reports or forms. I have often resorted to duplicating the queries I use and then converting them to Make or Append table queries. Access tables work much better with Excel. You can use the same query that you did to make the main report and append all the remaining queries to it. The draback is that once in Excel, a lot of manual or macro sorting is often required. Sorry I don't have a better answer. I will let you know if I think of one.

Sailalong