Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unanswered: having a problem coming up with what i want

    I am having a problem coming up with the information that I want when creating a query. Here is some info from my table:

    [Q1] &nbsp&nbsp [Q2] &nbsp&nbsp [Q3] &nbsp&nbsp [Q4] &nbsp&nbsp [Q5] &nbsp&nbsp [Q6] &nbsp&nbsp [Overall]

    &nbsp&nbsp 4 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp3 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 4 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 5 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 5 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 0

    &nbsp&nbsp4 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 4 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 4 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 3 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 2 &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 4

    what I am trying to do is calculate [Overall] from the average of Q1-Q6,
    if Q6=0 then [Overall] would be the average of Q1-Q5...I can't seem to get this right for some reason. I tried the following code and it set [Overall] to be 4.00 for all records. Could someone give me an idea of how I may accomplish this?

    UPDATE tbavg SET tbavg.[Overall Rating] = IIf([tbavg]![Q6]=0,(([tbavg]![Q1]+[tbavg]![Q2]+[tbavg]![Q3]+[tbavg]![Q4]+[tbavg]![Q5])/5),(([tbavg]![Q1]+[tbavg]![Q2]+[tbavg]![Q3]+[tbavg]![Q4]+[tbavg]![Q5]+[tbavg]![Q6])/6));

    thanks for any help you can give...i am running access 97

  2. #2
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    update table
    set overall = (q1+q2+q3+q4+q5+q6)/(IIF(q6=0,5,6))
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    thanks r123456, that works great. one thing that i'm seeing and i'm not sure why this is happening, is that when i am averaging numbers they are not coming out correct..example
    (4+3)/2=3.00 instead of 3.50..can you tell me if i am doing something wrong here?

Posting Permissions

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