Link to home
Start Free TrialLog in
Avatar of LUIS FREUND
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]);
Avatar of PatHartman
PatHartman
Flag of United States of America image

IIf(Nz(SumOfQTU_ORD,0) = 0, 0, 1 - (SumOfQTY_OPEN / SumOfQTY_ORD))
Avatar of LUIS FREUND
LUIS FREUND

ASKER

Works great....is there a way to format it as such:  100%, 0%, 92% for example?
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
AWESOME!  Thank you very much!
you're welcome :)