469,328 Members | 1,237 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,328 developers. It's quick & easy.

To calculate an average for a multiple record query.

abouddan
Hi all
I am working on an accounting project using MS Access 2000, that demands to calculate many fields in a spesific record.
The problem:
The query I am using returns many records and for each record 3 fields, let's say Data1, Data2, Data3.
In the form related to this query I have 5 text boxes.The three text boxes are bound to the query. The two other text boxes are for displaying calculations,
the first one displays the sum of the three fields (Data1, Data2, Data3), and the other one displays their average.
Note that just the positif numbers (>0) are calculated.
Ex: Data1=10
Data2=0
Data3=20
the result:
textbox4=30 (10+20)
textbox5=15 ((10+20)/2)

What I need is: How can I assing the result of summation and the average to the text boxes for each record?
Feb 14 '07 #1
5 15789
MMcCarthy
14,534 Expert Mod 8TB
Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT Data1, Data2, Data3, (Data1 + Data2 + Data3) As Total,
  2. IIf([Data1]=0, IIf([Data2]=0, IIf([Data3]=0,0),1,2),3) As AvgNum,
  3. IIf(AvgNum<>0,Total/AvgNum,0) As AvgTotal
  4. FROM TableName;
  5.  
Mary
Feb 14 '07 #2
NeoPa
32,181 Expert Mod 16PB
As Mary says, it is better to do the calculation in the Query itself. It is possible, though far more complex, to do it in the Record Source of the TextBoxes.
Feb 14 '07 #3
Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT Data1, Data2, Data3, (Data1 + Data2 + Data3) As Total,
  2. IIf([Data1]=0, IIf([Data2]=0, IIf([Data3]=0,0),1,2),3) As AvgNum,
  3. IIf(AvgNum<>0,Total/AvgNum,0) As AvgTotal
  4. FROM TableName;
  5.  
Mary

Thanks Mary for your support but this is not what I need.
First: a syntax error occurs when running the code. I think the problem is in the second line near "1,2)". The ",2" is extra...
Second: in the example I gave I said that Data1=10 and Data2=0 and Data3=20
so the average must be (10+20)/2. Using your code the result is (10+30)/3, witch's wrong.
Third: If the first field "Data1" is <> 0, he is not going to test the second field nor the third.
But thank you because using IIF in query was new to me. So I built my own query using IIF and it worked very well.

Expand|Select|Wrap|Line Numbers
  1. Select Data1, Data2, Data3, (Data1+ Data2+ Data3) as Total, 
  2. IIF (Data1 =0, 0, 1) as D1,
  3. IIF (Data2 =0, 0, 1) as D2,
  4. IIF (Data3 =0, 0, 1) as D3,
  5. (D1+ D2+ D3) as Avg,
  6. IIF (Avg= 0, 0, Total/Avg) as Average)
  7. From tablename
Feb 15 '07 #4
MMcCarthy
14,534 Expert Mod 8TB
Thanks Mary for your support but this is not what I need.
First: a syntax error occurs when running the code. I think the problem is in the second line near "1,2)". The ",2" is extra...
Second: in the example I gave I said that Data1=10 and Data2=0 and Data3=20
so the average must be (10+20)/2. Using your code the result is (10+30)/3, witch's wrong.
Third: If the first field "Data1" is <> 0, he is not going to test the second field nor the third.
But thank you because using IIF in query was new to me. So I built my own query using IIF and it worked very well.

Select Data1, Data2, Data3, (Data1+ Data2+ Data3) as Total,
IIF (Data1 =0, 0, 1) as D1,
IIF (Data2 =0, 0, 1) as D2,
IIF (Data3 =0, 0, 1) as D3,
(D1+ D2+ D3) as Avg,
IIF (Avg= 0, 0, Total/Avg) as Average)
From tablename
Glad you got it working. My fault I hadn't tested the IIf statement properly.

Mayr
Feb 15 '07 #5
NeoPa
32,181 Expert Mod 16PB
You can tidy it up a little more as D1 through D3 are not required more than once :
Expand|Select|Wrap|Line Numbers
  1. SELECT Data1,Data2,Data3,
  2.        (Data1+Data2+Data3) as Total,
  3.        IIf(Data1=0,0,1)+IIf(Data2=0,0,1)+IIf(Data3=0,0,1) As Avg,
  4.        IIF (Avg=0,0,Total/Avg) as Average)
  5. FROM tablename
Feb 15 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Steven Stewart | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.