Sorting by Group Totals

[cwh2724 via AccessMonster.com]

I am relatively new to Access and have gotten many answers from reading the
existing threads. I have not seen one problem specifically answered. I am
using Access 97.

I am generating a report of Employees and Errors with a count for each type
of Error. Here is the SQL that I am currently using to generate the report:

SELECT Employee, Error, Count(Error) as TotalErrors
FROM tbltmpUM
GROUP BY Employee, Error;

However, I need to sort the report descending by the total number of Errors
for each Employee. From what I have read I need to join a query to the one
above that is calculating the total number for the employee, maybe something
like

SELECT Employee, Count(*) as EmpCnt
FROM tbltmpUM
GROUP BY Employee;

I just can't figure out how to join the 2 since they both have a GROUP BY in
them. Do I need to create a temporary table in the new SELECT and then join
it to the other one?

Thanks in advance for any assistance that you can provide. This is an
awesome website!

 

[PhilT via AccessMonster.com]

Actually, you don't need to grouping from Query. Join both tables in Query,
and then grouping in report. From report you could Ascending or Descending
the sort. I think you should create the report from Query rather from Table,
in case you need to join more tables later on.

 

[cwh2724 via AccessMonster.com]

I must be having a 'senior moment'. I understand about grouping in report,
but don't I need to group in order to calculate the number of errors for an
employee? Can you provide specifics? Thanks so much!

 

[Duane Hookom]

You must save the second query with a name like "qgrpEmpErrCount". Then add
this query to the report's record source query and join the Employee fields.
You then have the EmpCnt field which can be used to sort in your report.

 

[cwh2724 via AccessMonster.com]

I finally got that to work. You all are wonderful to help out in this way.
Thank you so very much!