469,328 Members | 1,237 Online

# To calculate an average for a multiple record query. 42
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
abouddan
42 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

 7 posts views Thread by sql-db2-dba | last post: by 3 posts views Thread by Steven Stewart | last post: by 2 posts views Thread by Wayne Aprato | last post: by 2 posts views Thread by mscdex | last post: by 3 posts views Thread by larry | last post: by reply views Thread by SuzK | last post: by 12 posts views Thread by denveromlp | last post: by 5 posts views Thread by FishVal | last post: by 7 posts views Thread by Wiredboy | last post: by reply views Thread by ioana budai | last post: by 1 post views Thread by CARIGAR | last post: by reply views Thread by zhoujie | last post: by reply views Thread by shivambhatele | last post: by reply views Thread by suresh191 | last post: by reply views Thread by goatbishop | last post: by reply views Thread by listenups61195 | last post: by 1 post views Thread by WIPE | last post: by 5 posts views Thread by MGadAllah | last post: by