sph3rion
asked on
Percent of Total results from a query
Hello!
Here is my SQL satement thus far for ReasonsQuery:
SELECT DISTINCT SQqryGetOrgWorkedData.Reas ons, Count(SQqryGetOrgWorkedDat a.Reasons) AS CountOfReasons
FROM SQqryGetOrgWorkedData
GROUP BY SQqryGetOrgWorkedData.Reas ons
HAVING (((SQqryGetOrgWorkedData.R easons) Not In ("Closed Correctly")));
This returns a REASON and the total count of that reason within the SQqryGetOrgWorkedData query.
I would like another field, perhaps in this query, but it can be in another query that uses the ReasonsQuery if needed. This field would show the PERCENT for each REASON. So... if the total of all CountOfReasons = 100 and Reason1 = 10, I would like to know that Reason1 is %10 of the total. The "%" sign is not necessary but it would be nice.
Thanks in advance for all your help!
Here is my SQL satement thus far for ReasonsQuery:
SELECT DISTINCT SQqryGetOrgWorkedData.Reas
FROM SQqryGetOrgWorkedData
GROUP BY SQqryGetOrgWorkedData.Reas
HAVING (((SQqryGetOrgWorkedData.R
This returns a REASON and the total count of that reason within the SQqryGetOrgWorkedData query.
I would like another field, perhaps in this query, but it can be in another query that uses the ReasonsQuery if needed. This field would show the PERCENT for each REASON. So... if the total of all CountOfReasons = 100 and Reason1 = 10, I would like to know that Reason1 is %10 of the total. The "%" sign is not necessary but it would be nice.
Thanks in advance for all your help!
ASKER
'The expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.'
Is the error I get using that morpheus30.
It went to design view alright though...
Is the error I get using that morpheus30.
It went to design view alright though...
ASKER
'The expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.'
Is the error I get using that morpheus30.
It went to design view alright though...
Is the error I get using that morpheus30.
It went to design view alright though...
ASKER
'The expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.'
Is the error I get using that morpheus30.
It went to design view alright though...
Is the error I get using that morpheus30.
It went to design view alright though...
ASKER
'The expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.'
Is the error I get using that morpheus30.
It went to design view alright though...
Is the error I get using that morpheus30.
It went to design view alright though...
ASKER
Sigh... sorry about that, work network said page cannot be displayed
My bad, dude...
I did that carelessly. Try this...
SELECT DISTINCT SQqryGetOrgWorkedData.Reas ons, Count(SQqryGetOrgWorkedDat a.Reasons) AS CountOfReasons, 100 *(Count(SQqryGetOrgWorkedD ata.Reason s) / SELECT Count(*) FROM SQqryGetOrgWorkedData) AS PctOfReasons
FROM SQqryGetOrgWorkedData
GROUP BY SQqryGetOrgWorkedData.Reas ons
HAVING (((SQqryGetOrgWorkedData.R easons) Not In ("Closed Correctly")));
I did that carelessly. Try this...
SELECT DISTINCT SQqryGetOrgWorkedData.Reas
FROM SQqryGetOrgWorkedData
GROUP BY SQqryGetOrgWorkedData.Reas
HAVING (((SQqryGetOrgWorkedData.R
ASKER
Syntax error on: 100 *(Count(SQqryGetOrgWorkedD ata.Reason s) / SELECT Count(*) FROM SQqryGetOrgWorkedData)
Highlights SELECT
Just as a note, the total count of reasons is not necessarily 100... it's different each time and is probably going to be some odd number like 2567. If that has anything to do with anything, I'm stumped right now.
Highlights SELECT
Just as a note, the total count of reasons is not necessarily 100... it's different each time and is probably going to be some odd number like 2567. If that has anything to do with anything, I'm stumped right now.
Hey sph3rion,
What version of Access are you using?
What version of Access are you using?
ASKER
Access 2000 sir
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Better yet...
SELECT DISTINCT SQqryGetOrgWorkedData.Reas ons, Count(SQqryGetOrgWorkedDat a.Reasons) AS CountOfReasons, FormatPercent(Count(SQqryG etOrgWorke dData.Reas ons) / (SELECT Count(*) FROM SQqryGetOrgWorkedData), 2) AS PctOfReasons
FROM SQqryGetOrgWorkedData
GROUP BY SQqryGetOrgWorkedData.Reas ons
HAVING (((SQqryGetOrgWorkedData.R easons) Not In ("Closed Correctly")));
SELECT DISTINCT SQqryGetOrgWorkedData.Reas
FROM SQqryGetOrgWorkedData
GROUP BY SQqryGetOrgWorkedData.Reas
HAVING (((SQqryGetOrgWorkedData.R
ASKER
BINGO
I don't have FormatPercent though... is that Access XP?
I don't have FormatPercent though... is that Access XP?
ASKER
Thank you so very much by the way :)
hmmm...
The FormatPercent is supposed to be a standard VBScript function. As a matter of fact, I am using Access XP, but in Access 2000 format. It is quite possible that they made a change to support this function in XP and not in 2000.
Anyway, glad things worked out for you!
The FormatPercent is supposed to be a standard VBScript function. As a matter of fact, I am using Access XP, but in Access 2000 format. It is quite possible that they made a change to support this function in XP and not in 2000.
Anyway, glad things worked out for you!
FROM SQqryGetOrgWorkedData
GROUP BY SQqryGetOrgWorkedData.Reas
HAVING (((SQqryGetOrgWorkedData.R