Results 1 to 10 of 10

Thread: 100% Compliant

  1. #1
    Join Date
    Dec 2004
    Posts
    41

    Question Unanswered: 100% value in a select query?

    Quick question, for which an answer would be most appreciated ... I have a select query with the fields:
    PossibleErrors, ErrorsFound, ErrorPercent, CompliantPercent

    I sort this descending by CompliantPercent (ErrorsFound/PossibleErrors) to show the worker bees how they're stacking up, but the problem is that if ErrorsFound is null, so is the CompliantPercent. Anyone know a trick to show 100%?
    Thank you in advance!
    Last edited by Reg; 01-12-07 at 05:54.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    IIF (ISNULL(ERRORSFOUND),1,ERRORSFOUND/POSSIBLEERRORS)

    But you still get an error if it is possible for possibleerrors=0
    Inspiration Through Fermentation

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Which means you need to IIF the possibleErrors for the zero condition and set it to 1 ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I was gonna let Reg figure that one out on his own
    Inspiration Through Fermentation

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by RedNeckGeek
    I was gonna let Reg figure that one out on his own
    I had my doubts about that ... Consider the scope of this question ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Dec 2004
    Posts
    41

    Question iif null

    Thanks for the reply RedNeckGeek, and thanks for the sarcasm M Owen - always appreciated by us novices.

    If I understand correctly, I need to add an Iiif statement to the query... I would think it would be easiest to do it in the final percentage column. Therefore, if there is a null value in the "Compliant %" column I would want to display "100%". The following is my sql, and I would appreciate any help.
    Thanks so much!

    Code:
    SELECT realTable.UNIT AS [Unit Desc], realTable.PAS AS [PAS Code], realTable.[Total Of SSAN] AS [Total Assgn], realMatrix.ASSIGNED AS [Errors Found], (29*[TOTAL ASSGN]) AS [Possible Errors], [ASSIGNED]/[Possible Errors] AS [Error %], (1-realMatrix!ASSIGNED/[Possible Errors]) AS [Compliant %], *
    FROM realTable LEFT JOIN realMatrix ON realTable.PAS = realMatrix.PAS
    WHERE (((realTable.[Total Of SSAN])<>0 Or (realTable.[Total Of SSAN]) Is Not Null) AND ((realTable.[Unit Admin]) Is Not Null))
    ORDER BY realTable.[Unit Admin], realTable.UNIT;

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Reg
    Thanks for the reply RedNeckGeek, and thanks for the sarcasm M Owen - always appreciated by us novices.

    If I understand correctly, I need to add an Iiif statement to the query... I would think it would be easiest to do it in the final percentage column. Therefore, if there is a null value in the "Compliant %" column I would want to display "100%". The following is my sql, and I would appreciate any help.
    Thanks so much!

    Code:
    SELECT realTable.UNIT AS [Unit Desc], realTable.PAS AS [PAS Code], realTable.[Total Of SSAN] AS [Total Assgn], realMatrix.ASSIGNED AS [Errors Found], (29*[TOTAL ASSGN]) AS [Possible Errors], [ASSIGNED]/[Possible Errors] AS [Error %], (1-realMatrix!ASSIGNED/[Possible Errors]) AS [Compliant %], *
    FROM realTable LEFT JOIN realMatrix ON realTable.PAS = realMatrix.PAS
    WHERE (((realTable.[Total Of SSAN])<>0 Or (realTable.[Total Of SSAN]) Is Not Null) AND ((realTable.[Unit Admin]) Is Not Null))
    ORDER BY realTable.[Unit Admin], realTable.UNIT;
    No sarcasm ... Just a point of observation ... You haven't seen me be sarcastic yet ...
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Dec 2004
    Posts
    41
    Well hey, you're right. I can't figure it out on my own - why do you think I'm here asking?
    Whatever makes you feel better.

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    the difference is RedNeck wanted to help you find the answer, which normally means you gain a greater understand of it meaning that your less likely to have the problem again. however this is generally a slower process

    M Owen Gave you the answer, which might leave you at a loss next time you have a similar problem, but gets you sorted on this instance of the problem quicker.

    So feeling better has nothing to do with it, its about the best way to help you.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by m.timoney
    the difference is RedNeck wanted to help you find the answer, which normally means you gain a greater understand of it meaning that your less likely to have the problem again. however this is generally a slower process

    M Owen Gave you the answer, which might leave you at a loss next time you have a similar problem, but gets you sorted on this instance of the problem quicker.

    So feeling better has nothing to do with it, its about the best way to help you.
    I gave him/her the answer KINDA ... Still need to figure out how to use IIF and where to use it ... So, still some learning to do ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •