# Thread: having a problem coming up with what i want

1. Registered User
Join Date
Jan 2004
Posts
31

## 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. Registered User
Join Date
Sep 2003
Location
The extremely Royal borough of Kensington, London
Posts
778
update table
set overall = (q1+q2+q3+q4+q5+q6)/(IIF(q6=0,5,6))

3. Registered User
Join Date
Jan 2004
Posts
31
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
(([q1a]+[q1b])/2)
(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
•