Group by Calculated Sum

[wal50]

I've looked through the site and followed the suggestions but can't get it.

I have two tables. One with transaction costs for each employee (many
transactions for each employee) and the other that shows employees and their
Manager (one manager can have many employees). The query joining these works
and produces the correct grand totals. I want to base a report on this query
(not a cross tab) that summarizes the costs for each manager and sort the
report by the summarized total.
Following the suggestions I found here, I added a total field to the query.
That works. The report is grouped by manager (that works) but if I try to
sort it by the sum field at a higher level than manager - either as a group
or just a sort - I lose the Manager sum and Managers appear multiple times
with a total for each of their employees. I'm probably missing something
simple but don't know where to look any more.
Any direction would be appreciated.
wal50

 

[Ken Snell \(MVP\)]

Post the SQL statement of the query without sorting by calculated sum field
so that we can see what it looks like.

 

[KARL DEWEY]

I used this data --
[MGR-SUM] --
MGR EMP HRS
MGR1 BILL 6
MGR1 JOE 4
MGR1 JIM 3
MGR1 DICK 2
MGR2 SAM 3
MGR2 SUE 2
MGR2 BEE 4
MGR2 MARY 5
MGR2 FRED 6

This query to sum employee hrs per manager.
MGR-SUM_Total ---
SELECT [MGR-SUM].MGR, Sum([MGR-SUM].HRS) AS SumOfHRS
FROM [MGR-SUM]
GROUP BY [MGR-SUM].MGR;

This query to put total hours with manager and employee.
MGR-SUM_EMPs --
SELECT [MGR-SUM_Total].SumOfHRS AS MGR_SUM, [MGR-SUM_Total].MGR,
[MGR-SUM].EMP, [MGR-SUM].HRS
FROM [MGR-SUM_Total] INNER JOIN [MGR-SUM] ON [MGR-SUM_Total].MGR =
[MGR-SUM].MGR;

In the report I used the Grouping and Sorting. I used two headers, MGR-SUM
followed by MGR. I placed MGR-SUM and MGR in the MGR header.

And got this results ---
MGR: MGR1 MGR-SUM: 15
EMP: DICK HRS: 2
EMP: JIM HRS: 3
EMP: JOE HRS: 4
EMP: BILL HRS: 6
MGR: MGR2 MGR-SUM: 20
EMP: FRED HRS: 6
EMP: MARY HRS: 5
EMP: BEE HRS: 4
EMP: SUE HRS: 2
EMP: SAM HRS: 3

 

[wal50]

Thanks. These groups are always helpful.
wal50

I used this data --
[MGR-SUM] --
MGR EMP HRS
MGR1 BILL 6
MGR1 JOE 4
MGR1 JIM 3
MGR1 DICK 2
MGR2 SAM 3
MGR2 SUE 2
MGR2 BEE 4
MGR2 MARY 5
MGR2 FRED 6

This query to sum employee hrs per manager.
MGR-SUM_Total ---
SELECT [MGR-SUM].MGR, Sum([MGR-SUM].HRS) AS SumOfHRS
FROM [MGR-SUM]
GROUP BY [MGR-SUM].MGR;

This query to put total hours with manager and employee.
MGR-SUM_EMPs --
SELECT [MGR-SUM_Total].SumOfHRS AS MGR_SUM, [MGR-SUM_Total].MGR,
[MGR-SUM].EMP, [MGR-SUM].HRS
FROM [MGR-SUM_Total] INNER JOIN [MGR-SUM] ON [MGR-SUM_Total].MGR =
[MGR-SUM].MGR;

In the report I used the Grouping and Sorting. I used two headers, MGR-SUM
followed by MGR. I placed MGR-SUM and MGR in the MGR header.

And got this results ---
MGR: MGR1 MGR-SUM: 15
EMP: DICK HRS: 2
EMP: JIM HRS: 3
EMP: JOE HRS: 4
EMP: BILL HRS: 6
MGR: MGR2 MGR-SUM: 20
EMP: FRED HRS: 6
EMP: MARY HRS: 5
EMP: BEE HRS: 4
EMP: SUE HRS: 2
EMP: SAM HRS: 3

--
KARL DEWEY
Build a little - Test a little

I've looked through the site and followed the suggestions but can't get it.

I have two tables. One with transaction costs for each employee (many
transactions for each employee) and the other that shows employees and their
Manager (one manager can have many employees). The query joining these works
and produces the correct grand totals. I want to base a report on this query
(not a cross tab) that summarizes the costs for each manager and sort the
report by the summarized total.
Following the suggestions I found here, I added a total field to the query.
That works. The report is grouped by manager (that works) but if I try to
sort it by the sum field at a higher level than manager - either as a group
or just a sort - I lose the Manager sum and Managers appear multiple times
with a total for each of their employees. I'm probably missing something
simple but don't know where to look any more.
Any direction would be appreciated.
wal50

 

[wal50]

Thanks again Karl. Exactly what I wanted. I just used the first one since
that is all I needed.
The solution raised a couple of questions:
Both queries produces summaries. Suppose the detail (multiple entries under
each Emp) also needed to be reported. This would only really apply to the
second situation. Would this method work?
I also tried the second query even though I don't need it now. I think the
problem I am having is that Mgr is not in the same table as Hrs. I get the
Manager from a second (Employee) table. I can match Emp numbers from what
you called the Mgr-Sum table to the the Emp Number of an Employee table and
get the Employees manager. So, the way I read it, the Inner Join in the 2nd
query needs to be changed to ?.

I appreciate you help and am not looking for the specific solution, just for
a general direction to go in.

Thanks again,
wal50

I used this data --
[MGR-SUM] --
MGR EMP HRS
MGR1 BILL 6
MGR1 JOE 4
MGR1 JIM 3
MGR1 DICK 2
MGR2 SAM 3
MGR2 SUE 2
MGR2 BEE 4
MGR2 MARY 5
MGR2 FRED 6

This query to sum employee hrs per manager.
MGR-SUM_Total ---
SELECT [MGR-SUM].MGR, Sum([MGR-SUM].HRS) AS SumOfHRS
FROM [MGR-SUM]
GROUP BY [MGR-SUM].MGR;

This query to put total hours with manager and employee.
MGR-SUM_EMPs --
SELECT [MGR-SUM_Total].SumOfHRS AS MGR_SUM, [MGR-SUM_Total].MGR,
[MGR-SUM].EMP, [MGR-SUM].HRS
FROM [MGR-SUM_Total] INNER JOIN [MGR-SUM] ON [MGR-SUM_Total].MGR =
[MGR-SUM].MGR;

In the report I used the Grouping and Sorting. I used two headers, MGR-SUM
followed by MGR. I placed MGR-SUM and MGR in the MGR header.

And got this results ---
MGR: MGR1 MGR-SUM: 15
EMP: DICK HRS: 2
EMP: JIM HRS: 3
EMP: JOE HRS: 4
EMP: BILL HRS: 6
MGR: MGR2 MGR-SUM: 20
EMP: FRED HRS: 6
EMP: MARY HRS: 5
EMP: BEE HRS: 4
EMP: SUE HRS: 2
EMP: SAM HRS: 3

--
KARL DEWEY
Build a little - Test a little

I've looked through the site and followed the suggestions but can't get it.

I have two tables. One with transaction costs for each employee (many
transactions for each employee) and the other that shows employees and their
Manager (one manager can have many employees). The query joining these works
and produces the correct grand totals. I want to base a report on this query
(not a cross tab) that summarizes the costs for each manager and sort the
report by the summarized total.
Following the suggestions I found here, I added a total field to the query.
That works. The report is grouped by manager (that works) but if I try to
sort it by the sum field at a higher level than manager - either as a group
or just a sort - I lose the Manager sum and Managers appear multiple times
with a total for each of their employees. I'm probably missing something
simple but don't know where to look any more.
Any direction would be appreciated.
wal50