Using totals to calculate Percentages

G

Guest

Hi All,

I'm having a problem calculating percentages. It should have a simple
solution. I've got a table with 2500 lines of data and two queries. Query 1
calculates the totals of a number of columns. Query 2 groups the 2500 lines
of data into about 100 lines. I want to be able to use the totals from Query
1 to calculate the percentages for the corresponding fields in Query 2. I
keep getting errors involving subqueries.
If anyone could help me with this problem it would be greatly appreciated.
 
G

Guest

What is the error you are getting?
And did you create a third query that join query 1 and 2, to do the
calculation?
If you did, can you post the SQL?
 
G

Guest

I either get:
"You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)"
if I divide the grouped value by the total value (with the Total row in
Design as "Group By")

Or
"Subqueries cannot be used in the expression <expression>. (Error 3203)"
if add an aggregrate function (Sum) to the divide expression from above
(with the Total row in Design as "Expression")

The result I need to be in Query 2 and that's where I put it.

Thanks
 
G

Guest

Sorry, was away yesterday. For Query 2, where "Total Acknowledge Downtime
(hrs)" and "Percent of Acknowledge Downtime" are the relevent columns:
SELECT [Errors - Number of different turbines].[Log Number], [Error
ID].[Data Bin], [Error ID].Acknowledge, [Error ID].State, [Errors - Number of
different turbines].[Different Turbines], [Errors - Number of Errors].[Error
Frequency], Sum([Error Time (24 hr time)]/[Different Turbines]/[Error
Frequency]) AS [Average error time (24 hr time)], [Errors - Average Error
Day].Expr3 AS [Average Error Day], Sum([Acknowledge Downtime]/[Different
Turbines]) AS [Total Acknowledge Downtime (hrs)], Sum([Total Acknowledge
Downtime (hrs)]/[SumOfAcknowledge Downtime]) AS [Percent of Acknowledge
Downtime], [Total Acknowledge Downtime (hrs)]/[Error Frequency] AS [Average
Acknowledge Downtime (hrs)], Sum([Acknowledge Time (24 hr time)]/[Error
Frequency]/[Different Turbines]) AS [Average Acknowledge Time (24 hr time)],
[Errors - Average Error Day].Expr3 AS [Average Acknowledge Day]
FROM [Errors - DT Totals], (([Per Error Downtime] INNER JOIN ((([Errors -
Turbines] INNER JOIN [Errors - Number of Errors] ON [Errors - Turbines].[Log
Number] = [Errors - Number of Errors].[Log no]) INNER JOIN [Errors - Number
of different turbines] ON [Errors - Number of Errors].[Log no] = [Errors -
Number of different turbines].[Log Number]) INNER JOIN [Error ID] ON [Errors
- Number of Errors].[Log no] = [Error ID].[Log no]) ON ([Per Error
Downtime].[Log Number] = [Errors - Number of different turbines].[Log
Number]) AND ([Per Error Downtime].[Log Number] = [Error ID].[Log no]) AND
([Per Error Downtime].[Log Number] = [Errors - Turbines].[Log Number]) AND
([Per Error Downtime].[Log Number] = [Errors - Number of Errors].[Log no]))
INNER JOIN [Errors - Average Error Day] ON ([Error ID].[Log no] = [Errors -
Average Error Day].[Log no]) AND ([Per Error Downtime].[Log Number] = [Errors
- Average Error Day].[Log no])) INNER JOIN [Errors - Average Acknowledge Day]
ON ([Error ID].[Log no] = [Errors - Average Acknowledge Day].[Log no]) AND
([Per Error Downtime].[Log Number] = [Errors - Average Acknowledge Day].[Log
no])
GROUP BY [Errors - Number of different turbines].[Log Number], [Error
ID].[Data Bin], [Error ID].Acknowledge, [Error ID].State, [Errors - Number of
different turbines].[Different Turbines], [Errors - Number of Errors].[Error
Frequency], [Errors - Average Error Day].Expr3, [Errors - Average Error
Day].Expr3
ORDER BY [Errors - Number of different turbines].[Log Number];

And for Query 1 where "SumOfAcknowledge" Downtime is the relevent Column:

SELECT Sum([Per Error Downtime].[Acknowledge Downtime]) AS [SumOfAcknowledge
Downtime], Sum([Per Error Downtime].[Repair Downtime]) AS [SumOfRepair
Downtime], Sum([Per Error Downtime].[Total Downtime]) AS [SumOfTotal Downtime]
FROM [Per Error Downtime];

Thanks again
 
G

Guest

Try two things
1. Run each query seperatly to see if you get any error
2. If you have no problem with 1, start removing field by field from the
query until you find the field that cause the problem

--
I hope that helped
Good luck


Kirk said:
Sorry, was away yesterday. For Query 2, where "Total Acknowledge Downtime
(hrs)" and "Percent of Acknowledge Downtime" are the relevent columns:
SELECT [Errors - Number of different turbines].[Log Number], [Error
ID].[Data Bin], [Error ID].Acknowledge, [Error ID].State, [Errors - Number of
different turbines].[Different Turbines], [Errors - Number of Errors].[Error
Frequency], Sum([Error Time (24 hr time)]/[Different Turbines]/[Error
Frequency]) AS [Average error time (24 hr time)], [Errors - Average Error
Day].Expr3 AS [Average Error Day], Sum([Acknowledge Downtime]/[Different
Turbines]) AS [Total Acknowledge Downtime (hrs)], Sum([Total Acknowledge
Downtime (hrs)]/[SumOfAcknowledge Downtime]) AS [Percent of Acknowledge
Downtime], [Total Acknowledge Downtime (hrs)]/[Error Frequency] AS [Average
Acknowledge Downtime (hrs)], Sum([Acknowledge Time (24 hr time)]/[Error
Frequency]/[Different Turbines]) AS [Average Acknowledge Time (24 hr time)],
[Errors - Average Error Day].Expr3 AS [Average Acknowledge Day]
FROM [Errors - DT Totals], (([Per Error Downtime] INNER JOIN ((([Errors -
Turbines] INNER JOIN [Errors - Number of Errors] ON [Errors - Turbines].[Log
Number] = [Errors - Number of Errors].[Log no]) INNER JOIN [Errors - Number
of different turbines] ON [Errors - Number of Errors].[Log no] = [Errors -
Number of different turbines].[Log Number]) INNER JOIN [Error ID] ON [Errors
- Number of Errors].[Log no] = [Error ID].[Log no]) ON ([Per Error
Downtime].[Log Number] = [Errors - Number of different turbines].[Log
Number]) AND ([Per Error Downtime].[Log Number] = [Error ID].[Log no]) AND
([Per Error Downtime].[Log Number] = [Errors - Turbines].[Log Number]) AND
([Per Error Downtime].[Log Number] = [Errors - Number of Errors].[Log no]))
INNER JOIN [Errors - Average Error Day] ON ([Error ID].[Log no] = [Errors -
Average Error Day].[Log no]) AND ([Per Error Downtime].[Log Number] = [Errors
- Average Error Day].[Log no])) INNER JOIN [Errors - Average Acknowledge Day]
ON ([Error ID].[Log no] = [Errors - Average Acknowledge Day].[Log no]) AND
([Per Error Downtime].[Log Number] = [Errors - Average Acknowledge Day].[Log
no])
GROUP BY [Errors - Number of different turbines].[Log Number], [Error
ID].[Data Bin], [Error ID].Acknowledge, [Error ID].State, [Errors - Number of
different turbines].[Different Turbines], [Errors - Number of Errors].[Error
Frequency], [Errors - Average Error Day].Expr3, [Errors - Average Error
Day].Expr3
ORDER BY [Errors - Number of different turbines].[Log Number];

And for Query 1 where "SumOfAcknowledge" Downtime is the relevent Column:

SELECT Sum([Per Error Downtime].[Acknowledge Downtime]) AS [SumOfAcknowledge
Downtime], Sum([Per Error Downtime].[Repair Downtime]) AS [SumOfRepair
Downtime], Sum([Per Error Downtime].[Total Downtime]) AS [SumOfTotal Downtime]
FROM [Per Error Downtime];

Thanks again

Ofer said:
Can you post the SQL you have?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculate percentage 2
Problem Creating Totals Query 1
Percentages in group totals 3
Calculating Problem 4
Calculate 1
Calculating Percentages 2
calculating running totals 2
Calculating percentages 3

Top