Subreport total on main report shifts to next page

[bruegel]

I’ve created a report which contains two nested subreports. There is a field on the main report bound to a total from the two nested subreports.



The report contains financial statements for 10 clients

Client 1 – The total on the main report is correct for that client

Client 2 – The total on the main report is the same as for Client 1

Client 3 - The total on the main report is the correct total for Client 2

Client 4 - The total on the main report is the correct total for Client 3

And so on

Please can anyone give me a clue on where to look for my error?

Many Thanks

Pieter

If it helps

=([Reports]![Record_Statement_Complete]![Online_Statement_Part1]![Online_Statement_Part2]![TotalOnline])

[June7]

The expression doesn't make sense to me. Are _Part1 and _Part2 the two subreports? Is this really a report/subreport/subsubreport arrangement?

If you want to provide db for analysis, follow instructions at bottom of my post.

[bruegel]

Yes this is really a report/subreport/subsubreport arrangement?

Music can be downloaded either by album or song.

I created a report based on the income from albums and another based on the income from songs. Because the criteria for pulling out the data from the accounting are different, two sets of queries had to be built from different tables.

The income from records is on report online_sales_part1 and the income from songs is on online_sales_part2

The nested query structure to bring these together onto a single report would be extremely convoluted so a sub report (online_sales_part2) linked to the parent (online_sales_part1) using the ArtistName with a total summed from both reports (named TotalOnline) for both sets of sales was the simplest solution.

This worked fine.

I was then asked if I could incorporate the results from this exercise into the main report for each artists other types of sales (an established report of many years standing with which we never had any problems).

I linked the online_sales_part1 report to the main report Record_Statement_Complete as a child parent using ArtistName again.

In order to bring the TotalOnline figure into the summary page of the Record_Statement_Complete report I wrote the expression

=([Reports]![Record_Statement_Complete]![Online_Statement_Part1]![Online_Statement_Part2]![TotalOnline])

with the results as per my initial question. As I say it brought the correct totals into the main report except the first total appeared in the first two Clients report sections and the rest shifted into the following.

I don't see anything wrong with the expression but that I suppose is because it works so far as the numbers are concerned. I guessed there was some artefact within Access such as sorting and grouping, forcing pages or even grow/shrink properties where I should direct my attention and was asking for help in where to look first.

Sending a db for analysis would be difficult because the tables come from other db's in order to prevent any changes to them and I would have to effectively build a new db for examination.

Pieter

[June7]

Afraid this is a situation I can't analyse without getting intimate with database. I don't know that report Grouping & Sorting would be useful with this report structure but Access Help has guidelines on that reporting functionality.

[bruegel]

I rebuilt the reports importing the data and using a table as data source instead of queries. It produces the same result.

The report needs reformatting but this is largely cosmetic. The problem field is stuck at the end of the main report section on the first page or second / third page of the report. I've left it raw, unformatted and by itself on the left side so it stands out.

I hope this downloads ok and is functional.

Also I apologise I misnamed the reports in the previous post calling them one name at the beginning and another at the end in the expression which won't have helped. I'd spent so many hours with the problem my brain was fried.RGC_Online - Access Forum.zip

Thanks for the help

Pieter

[June7]

First thing I see odd is table Records has RecordCatNumber field as primary key but RecordName is saved in S51Agg_RGC_H2_2012. There are multiple records in both tables with the same title/name. The query S51Agg_Records_LabRoy joins these tables on record title/name fields (RecordName and Title). The query doesn't know which title record in each table goes with title record in other table with same title/name so it joins all records in each table to all records with same name in other table. For instance, there are 3 records in Records with RecordName of 'Andrew Weatherall Vs. The Boardroom' and 7 records in S51Agg_RGC_H2_2012 with the same name. Without the grouping and filtering, the query would show 21 records with this title/name.

Why is PK not saved as FK?

This might be contributing to reporting errors. I am reluctant to continue analysis until this relationship issue is resolved.

Suggest avoid quote marks in data values.

[bruegel]

RGC_Online - Access Forum - Wednesday - TablesOnly.zip

I've been working on this for several days and am still flummoxed.

I have converted the queries on which the reports are based into tables and checked that each table is composed only of unique values.

I still have the duplicate total for the first two clients and the subsequent totals each shifted along to the next client.

I've added the revised db