Writing a sum from a query into a new table

[roar58]

Hi!
I have created I database that I have been using for some time, but need to automatize a routine. I have tried to make an example that is simple: There are six students. Each student belong to one language group (English and French). They have a certain amount of study hours in their group. I summarize the hours they have, and get a result per group. All this by making two tables: Students and Grops, and a query that summarize the number of hours.

I need to write this sum in a third table which I call "SumHours2". But how can I convert datas and move them from a formula in the field "SumAvHours" (or SumOfHours) to the data field "SumHours2"? 2 attachments.


Regards from Norway

[June7]

Saving calculated data is usually a bad idea, especially aggregate data. This data should be calculated when needed, not saved to table.

Why do you want to repeat data?

[roar58]

Saving calculated data is usually a bad idea, especially aggregate data. This data should be calculated when needed, not saved to table.

Why do you want to repeat data?

The students come from several cities, and I am going to invoice the cities for the amount of hours used per student. In group 1 student 1 spend 2 hours, student 2 - 3 hours, student 3 - 4 hours. In total there are 9 hours with education, but the max for the group is 4 hours (the student that gets most study hours) - therefore the max is also a figure I plan to update for the group in the new table. I will then invoice the cities: Of the max of 4 hours I will invoice city 1 (the city that belongs to student 1) 2/9. City 2: 3/9, City 4: 4/9. See new database version with the max.

I have 50 groups to maintain in this way. Up to now I have written the totals and the max for the grops in a second table (as shown), but hopefully there will be a better and more automatic solution. Usually the changes are few, but it would be much more elegant to press an Update button than the manual work I am doing now.

With regards
Roar58

[June7]

My point is it doesn't matter how many groups or students or where they come from, a query should be adequate to provide the summary data. Calculate when needed. Any query procedure that could generate the values for saving to table would be the same used to just dynamically report the calculated results. Saving aggregate data is contrary to relational database best practice. Properly structured database should not require saving aggregate data. I don't see any city info in the db.

[roar58]

My point is it doesn't matter how many groups or students or where they come from, a query should be adequate to provide the summary data. Calculate when needed. Any query procedure that could generate the values for saving to table would be the same used to just dynamically report the calculated results. Saving aggregate data is contrary to relational database best practice. Properly structured database should not require saving aggregate data. I don't see any city info in the db.

I think I see your point. If the datas are correctly structured and the queries are well organised, there is no need to retype or copy into a new table. I think I haven't got the idea to solve it, so far, by structure and queries - perhaps I can ask for a way out here? I enclose an Excel-file which shows how I want the reports. There are also other criterias: 10 Levels of class, 3 types of groups, which will also need reports, but if I can make the two first reports, I think I will have the necessary tools to proceed. My main problem: Is there a way to calculate the InvoiceHours in a query and then use them in the the following reports?
The help is much appreciated!

[June7]

That output could be generated by a report using Grouping & Sorting features with aggregate calcs in group and report footers.

[roar58]

I think I see your point. If the datas are correctly structured and the queries are well organised, there is no need to retype or copy into a new table. I think I haven't got the idea to solve it, so far, by structure and queries - perhaps I can ask for a way out here? I enclose an Excel-file which shows how I want the reports. There are also other criterias: 10 Levels of class, 3 types of groups, which will also need reports, but if I can make the two first reports, I think I will have the necessary tools to proceed. My main problem: Is there a way to calculate the InvoiceHours in a query and then use them in the the following reports?
The help is much appreciated!

I have made a report with a sum field and a max field in the footer of each group , but what about the precentages for each record - do I also put those in the footer? Can you give me an idea on how to do this?

[roar58]

Now I see the principle! Thank you very much for valuable help.
Regards from Norway, Roar 58

[June7]

Glad you figured it out. I was just about to look at the first attempt.

[roar58]

Glad you figured it out. I was just about to look at the first attempt.

Hello! I came a great step further, but had hoped that I didn't have to ask again so quickly.
I successfully have made calculations to find the percentages of the total hours, but when I try to summarize these aggregated datas, it doesn't work.
I can easily summarize a detailed field that comes from the query, but what about an aggregated data field? Is there another way to summarize this in the report?
Regards...

[June7]

You want to use a sum of the percentage calcs to verify they add up to 100%. Sorry, cannot use an aggregate function on a textbox. As you noted, must be an actual field in the query. Could do this:

=Sum([Hours])/[SumHours]

[roar58]

You want to use a sum of the percentage calcs to verify they add up to 100%. Sorry, cannot use an aggregate function on a textbox. As you noted, must be an actual field in the query. Could do this:

=Sum([Hours])/[SumHours]

I'm afraid that I have to move back to the starting point then. I need to "play" with the aggregate datas from the report, e.g. summarize the calculations made in the report. It would be good if I could make the calculations in the query, but I don't see I can do that. When I invoice the cities, I need to pick out Invoice Hours (calculated field) and summarize them, city by city. I don't see how I can do this if I don't enter the Sum's and the Maks's into a new table - as I described earlier.

[June7]

These are two different reports. Build another report that does the city summary. Print the subject summary and city summary reports separately or as subreports on another report object.

[roar58]

These are two different reports. Build another report that does the city summary. Print the subject summary and city summary reports separately or as subreports on another report object.

I do have problems with the City report. I imagine that I have to build the report from the datas found in the subject report. Am I wrong?
Let me show how I solved this up to now - a solution which I am not happy with at all. I type the Sum's and the Max's of each group in a separate table. Of course I don't want to do this. But then I get the InvHours in a field of the query. I use the footer of the Subject report to list the cities and get the totals of each city.

3 attachments.
Regards

[June7]

How do you determine the MaksHrs2 value in SumHours2 table? It looks like the maximum value of all records for each subject. This query generates that and looks like the SumHours2 table and can be used in Query1 instead of that table:

SELECT Student.FkGroup, Max(Student.Hours) AS MaksHours, Sum(Student.Hours) AS SumHours
FROM Student
GROUP BY Student.FkGroup;

Query 1 has the information needed to build both reports. Just change the Grouping & Sorting orders in each report.


What is missing in this data is date values so the output can be filtered by a date range.