Calculating percent complete in a query in Access

LUIS FREUND
LUIS FREUND used Ask the Experts™
on
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]);
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
IIf(Nz(SumOfQTU_ORD,0) = 0, 0, 1 - (SumOfQTY_OPEN / SumOfQTY_ORD))

Author

Commented:
Works great....is there a way to format it as such:  100%, 0%, 92% for example?
Distinguished Expert 2017
Commented:
Just nest it again:

Format(IIf(Nz(SumOfQTU_ORD,0) = 0, 0, 1 - (SumOfQTY_OPEN / SumOfQTY_ORD)), "percent")

If that isn't valid, just look up the options for Format().  I'm not at my computer.

Author

Commented:
AWESOME!  Thank you very much!
Distinguished Expert 2017

Commented:
you're welcome :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial