LUIS FREUND
asked on
Calculating percent complete in a query in Access
Calculating percent complete in a query in Access:
Please see below what the percentages should be under "PN_COMPLETE_PERCENTAGE"
PN SumOfQTY_ORD SumOfQTY_OPEN PN_COMPLETE_PERCENTAGE
37TB10F 65 65 0%
37TB12F 12 0 100%
37TB16F 55 55 0%
37TB4F 65 62 5%
38TB4F 4 0 100%
38TB6F 52 0 100%
39TB10 119 109 8%
39TB12F 100 90 10%
39TB8F 20 15 25%
I would like the Sql Statement below to get me the results above.
SELECT Trim([PART_NUMBER]) AS PN, Sum(tbl_PART.QTY_ORD) AS SumOfQTY_ORD, Sum(tbl_PART.QTY_OPEN) AS SumOfQTY_OPEN
FROM tbl_PART
GROUP BY Trim([PART_NUMBER]);
Please see below what the percentages should be under "PN_COMPLETE_PERCENTAGE"
PN SumOfQTY_ORD SumOfQTY_OPEN PN_COMPLETE_PERCENTAGE
37TB10F 65 65 0%
37TB12F 12 0 100%
37TB16F 55 55 0%
37TB4F 65 62 5%
38TB4F 4 0 100%
38TB6F 52 0 100%
39TB10 119 109 8%
39TB12F 100 90 10%
39TB8F 20 15 25%
I would like the Sql Statement below to get me the results above.
SELECT Trim([PART_NUMBER]) AS PN, Sum(tbl_PART.QTY_ORD) AS SumOfQTY_ORD, Sum(tbl_PART.QTY_OPEN) AS SumOfQTY_OPEN
FROM tbl_PART
GROUP BY Trim([PART_NUMBER]);
IIf(Nz(SumOfQTU_ORD,0) = 0, 0, 1 - (SumOfQTY_OPEN / SumOfQTY_ORD))
ASKER
Works great....is there a way to format it as such: 100%, 0%, 92% for example?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
AWESOME! Thank you very much!
you're welcome :)