Item in the query as a percentage of a total

[roar58]

Hi again!
Re. the report in the file roar58 (1).accdb had a bug: If a group had students from only one city, then there will be a miscalculation in the total of the report. Dok1.pdf Is there a way to solve this?

Greetings, Roar58

[June7]

Shouldn't make any difference how many cities in each group. It's the same forumula. What is the correct formula? How did you get the red values - manual calc? Are you using rounded values in manual calc? The report is not rounding at any step in the calcs. I can't get the numbers you show in red.

[roar58]

Hi again!
Roar58.pdf There is only a very small difference between the report I need and the report I received some days ago. I have tried to visualize this difference by two tables in the file attached. The result of the table no. 2 is the same as in the report that I received. I will need a report like the first table. (I didn't discover the mismatch at once, because the difference of the results was only 0,3 hours.)

Greetings from Roar58

[June7]

The sample project I provided you has the same group calcs as Table1 but the city calcs are same as Table2. How can that be? What is the formula you use for the city calcs?

[roar58]

The sample project I provided you has the same group calcs as Table1 but the city calcs are same as Table2. How can that be? What is the formula you use for the city calcs?

The formula of the city calcs: I just make an addition of each record of Bergen: Share.Hours.Student1FromBergen + ....Student2FromBergen [...] = Sum ShareHours.Students from Bergen
And then ShareHours.Student1FromOslo + ....Student2From Oslo + ..... = SumShareHours.StudentsFromOslo
If one can summarize the text boxes in the right column in the bottom of the report, and group the text boxes, as you can for a field, then it would not be a problem. Hope for a solution....and thanks again.

Roar58

[June7]

I don't understand your formula. I built project based on the data sample in your post. I am obviously missing some tables. Without them I can't fully test your data and forumlas. So unless you want to provide, I can't do anything else.

[roar58]

Hi again!
I see that the Student field may have caused som misunderstandings. Because I have named "Student 3" as coming from both Oslo and Bergen. That is wrong. There are 4 students, Student 1 and 4 from Oslo, and Student 2 and 3 from Bergen. The project is that Bergen has to pay to it's students and Oslo must pay for students coming from Oslo.

I tried to upload my Excel file, but I guess the forum was not open for such files. I try again with two pdf's. The first one with formulas and the second one with the results. I need a report from Table1. Thank you for your patience!

Roar58

[roar58]

Roar58-4.pdf The last attachements were perhaps difficult to read. In this attachment I have also written the Titles of the columns and rows. The main difference between the two tables: In table 1 the ShareHours are calculated on the basis of the local MaxHours of the group. In table 2 the ShareHours are calculated on the basis of the MaxHours of all the groups. (I need table 1) I perhaps need to think totally different to be able to make such a report. You have previously mentioned subreports... will there perhaps be more possibilities if I use subreports?
Greetings, Roar58

[June7]

If Excel won't upload directly, zip it. But I get the issue from the pdf.

I corrected data to reflect the proper student designations. I still get the same mixed results.

I don't understand your city totals from Table1. You are summing calcs that use the group max to determine share hours, not the combined city max as I did. I tried using the max NumHours but the result was way off. You are right that because there is no cell referencing possible in the Access report for each student row, can't selectively sum the way you did in spreadsheet. Review this revision which does accomplish the summary calcs you show. EDIT: purpose served, file removed.

EDIT: I was testing calcs and composing as you posted so did not see your last post first. The new calcs I show might be sufficient. Would resort to subreports if had a lot of groups and cities. If there are more than the 2 groups, will need calcs for each group.

[roar58]

Hi again!
There it is! The left columns are now correct and precicely what I need! But even if there are only four cities, there are many groups - up to 30 in each report. The Max Formula at the bottom makes it impossible to use this report because there are new groups all the time - and I cannot redesign it all the time.

There is an option to define the max field into the group definition when I type in the datas of a group. Then the ShareHours will not be a text box, but a field which can be summarized in the bottom of the report.

Another option will be the sub report as I understand, so I have started to read something about this subject on the Internet. Would be thankful for hints in this area - if you feel that you can spend more time on this now....my offer sent som days ago still remains open.
Thanks a lot again!

Greetings from Roar58

[June7]

I will let you explore. Maybe start a new thread (can include reference to this one) if encounter issue with subreport approach.

[roar58]

I'll do it right away!
Roar58

[roar58]

Hi again!
After having worked with reports (and also having checked the subreports) for this query for one week, I am rather convinced that I have to use the queries rather than reports for the calculations that I need.

I think I need to first calculate the totals and max for the group - and then append the datas into the group table (I have worked with append tables a bit, but not much). I would like to do the calculation for all the groups of course (in one operation for all the groups), but I first perhaps have to concentrate on how I do it for one group?

I enclose a zipfile with an Excel sheet where I have cut the datas and the tables to the bone to explain the issue. Thankful for any input here....

Greetings from Roar58

[roar58]

Hi!
Something was wrong with the data model above. Sorry! I come back with a correct data model.
Roar58