How do I get the Average Speed Answered Based on the sum of two columns?

[msquery]

I am trying to get the Total average speed answered (ASA) based on two columns sums. (i.e. sum of calls handled and sum total speed answer). I need to get the total avg speed ans (formula is sum of total speed answer (tsa) divided by sum calls handled). I did an average query for asa but it gives me the avg for the rows(records) of the asa and not of the two columns. I need it for the two columns above. I was trying to change the formula in report design view, but I am not coming out with the correct answer. Are there any suggestions?

Please help!

 

[Murli]

Instead of average use expression to get the average of the sum of two columns.

SELECT Sum([tele].[calls handled]) AS [SumOfcalls handled], Sum([tele].[total speed answer]) AS [SumOfanswers speed], [SumOfanswers speed]/[SumOfcalls handled] AS Expr1
FROM tele;

Let me know if that works.

 

[msquery]

Query Only no SQL Statements

With this query, I did not use SQL. I wanted to keep it simple for now. How would I do it without using SQL statements.

 

[Pat Hartman]

All queries use SQL. Switch your query from design view to SQL view and try to make the change. Otherwise, post the SQL you see and someone will help you.

 

[msquery]

Thank you so much! The formula did work. Currently, I have it set up as two queries one for all the sums and one for the records. How can I bring both together on one report? When I try to add the ASA on the TSA query it says I can't do a subquery. Both are expressions. What do I need to do to bring all sum of calls handled and client individual records of calls handled together,TSA records and sum. and ASA records and sums.

 

[Pat Hartman]

It would be better to do the summing in the report itself than to do it in queries and try to join them later.