Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2015
    Posts
    51

    Unanswered: YES/NO Values to %, and Averaging

    I have a yes/no field on a table representing if someone is late or on time. I have turned this yes/no into a percentage in a query averaging each person being on time with in a date range.
    ontime%: FormatPercent(Avg(Abs([Yes/No])))
    MainTable:
    EMPL ONtime
    1 Yes
    2 Yes
    3 Yes
    4 Yes
    5 Yes
    6 No
    7 No
    8 No
    9 No
    10 No
    1 Yes
    2 Yes
    3 Yes
    4 Yes
    5 No
    6 No
    7 No
    8 No
    9 No

    Query:

    EMPL ontime%
    1 100.00%
    10 0.00%
    2 100.00%
    3 100.00%
    4 100.00%
    5 50.00%
    6 0.00%
    7 0.00%
    8 0.00%
    9 0.00%

    What I would like to do is average the total on time percentage for all employees. Each time I try to enter this into my report it says that the expression in to difficult to work out?? avg([ontime%])


    Any thoughts here?

  2. #2
    Join Date
    Jul 2015
    Posts
    51

    yes/no: changing values

    So I have figured this out to a point, however because access uses the true/false values for yes/no as well yes=-1 and no=0, this is making my final percentage a negative. Is there a way to change the values of yes/no to say yes=1 instead?

  3. #3
    Join Date
    Jul 2015
    Posts
    51

    Solution? is there a better way?

    Okay here is my solution: anyone see a better way?
    Click image for larger version. 

Name:	OntimePercentage.PNG 
Views:	1 
Size:	36.0 KB 
ID:	16438Click image for larger version. 

Name:	OntimePercentage2.PNG 
Views:	4 
Size:	35.9 KB 
ID:	16439

Posting Permissions

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