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]);
LUIS FREUNDAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor 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.
0
 
PatHartmanCommented:
IIf(Nz(SumOfQTU_ORD,0) = 0, 0, 1 - (SumOfQTY_OPEN / SumOfQTY_ORD))
0
 
LUIS FREUNDAuthor Commented:
Works great....is there a way to format it as such:  100%, 0%, 92% for example?
0
 
LUIS FREUNDAuthor Commented:
AWESOME!  Thank you very much!
0
 
PatHartmanCommented:
you're welcome :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.