Item in the query as a percentage of a total

[roar58]

Hi!


I work in a business manager at a school. In an Excel worksheet this table is easy to create, but how do I do it in Access? 3 students participate in a group. In the input field I type number of hours they use in the group. In an Access query, is there a way to get the percentage of each student of the total number of hours?

Group 1	Number of hours	Percentage
Student 1	7	58,33 %
Student 2	3	25,00 %
Student 3	2	16,67 %
Total	12	100,00 %

Greetings, roar58

[June7]

Assume a table structure like:

Group	Student	NumHours
1	1	7
1	2	3
1	3	2

Try this query:
SELECT Group, Student, Sum(NumHours) AS SumOfNumHours, (SELECT Sum(NumHours) As GroupHrs FROM Table1 GROUP BY Group) AS TotGroup, Sum([NumHours]/[TotGroup]) AS Pct
FROM Table1
GROUP BY Group, Student;

[roar58]

Hi! Thank you, June7, for a very quick reply!
I have tried the query - and it does indeed function. Hope it will also function when I shall install it in a complex database structure.
I have never been using the forum before and this was indeed a very useful way to solve a problem I couldn't sort out myself. So again, thanks a lot!
Greetings from Roar58.

[roar58]

Hi! I have worked with it, and seen that I have a need for a slightly more advanced query. The last query had only one group as an option. Now I want a query with more groups, so that I can get the result below. Anyone who can help me making such a query - or do I have to solve the problems within "Forms"?

	Group	Student	Number of Hours	Percentage
	1	1	7	58,33 %
	1	2	3	25,00 %
	1	3	2	16,67 %
Total			12	100,00 %
	2	1	10	11,76 %
	2	2	20	23,53 %
	2	3	15	17,65 %
	2	4	40	47,06 %
Total			85	100,00 %

and so forth....

Greetings from Roar58

[June7]

Build a report and use its Grouping and Sorting with aggregate calcs functionality. Use the same query as RecordSource for the report.

[roar58]

Forgive me, June7, for my relatively small knowledge in queries. The query doesn't function if I enter datas of one more group (Group 2). I guess this won't help if make a report (which I have tried) - I guess the source of the report must be a query that can function. Any way to do this? I can make several queries and several reports, one for each group, but this doesn't look very elegant? Thankful for your advice!

Assume a table structure like:

Group	Student	NumHours
1	1	7
1	2	3
1	3	2

Try this query:
SELECT Group, Student, Sum(NumHours) AS SumOfNumHours, (SELECT Sum(NumHours) As GroupHrs FROM Table1 GROUP BY Group) AS TotGroup, Sum([NumHours]/[TotGroup]) AS Pct
FROM Table1
GROUP BY Group, Student;

[June7]

Attached is example with report Grouping & Sorting and aggregate calcs. EDIT: Purpose served, file removed.

Will there be multiple records for each student in a group?

[roar58]

No, there will be just one record for each student in a group, so your report fits perfect - as I can see now. I have been working with Access for some years, but I have always produced reports with Queries as the source. Now I learned that I can also do it more directly from a table.
Thanks a lot, again!

[roar58]

Hi again June7!
I do still have some problems when transferring some fields into my own database.
In your report there is a field named [GroupTotal] which is defined as Sum (NumHours) at group level.
But where is this field defined? I cannot find it's definition. When I go to my own report I don't succeed in defining this field.

Greetings from Roar58

[June7]

It is not a field, it is name of textbox.

[roar58]

Thank you again! This was very helpful after having seached for a field for some hours - the solution came in your one-liner! Greetings from Roar58

[roar58]

Hi again!
My project is extended day by day when I get back to the end users of the report. Now I have this report I would like to produce:

	Group	Students	City	NumHours	Percentage	MaxHours	ShareHours
	1	1	Oslo	7	58,33 %		4,08
	1	2	Bergen	3	25,00 %		1,75
	1	3	Bergen	2	16,67 %		1,17
Total				12	100,00 %	7	7,00
	2	1	Oslo	10	11,76 %		4,71
	2	2	Bergen	20	23,53 %		9,41
	2	3	Oslo	15	17,65 %		7,06
	2	4	Bergen	40	47,06 %		18,82
Total				85	100,00 %	40	40,00
Total report							47,00
	Sum		Oslo				15,85
	Sum		Bergen				31,15
	Total						47,00

The last things that happened to the report was this: The percentages should calculate a share of the max amount of hours per group. To produce the share amounts of hours I have made. But it's harder to summarize what is text boxes and not fields - the far right column. Is there a way to summarize these text boxes and to make the sums "7,00" and "40,00". I get error message when I try =Sum ([TextBox18])

The students come from various cities (the two biggest cities in Norway). In the end of the report I also want to summarize the hours per city. I have tried a select sentence, but here also I think there is problems because I want to summarize a text box and not a field. Any way to solve this?

Greetings from Roar58

[June7]

See if this version is getting there. Are there only two cities in the data? If you want a lot more than two cities in the report totals, consider a subreport. EDIT: Purpose served, file removed.

[roar58]

See if this version is getting there. Are there only two cities in the data? If you want a lot more than two cities in the report totals, consider a subreport.

Thank you again. It helps a lot! There are at least four cities, so I consider your proposal. I have never worked with subreports before, so I guess I have to read something about them then. My colleagues are very enthusiastic over the database with these new possibillities and and will transform their working situation a lot for the better!

[June7]

If only 4 or 5 cities would not be so bad to use the method shown in sample. But would have to modify report each time a city added, if that is possibility. Good luck!