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?

[MMcCarthy]

Try this ...

SELECT Data1, Data2, Data3, (Data1 + Data2 + Data3) As Total,
IIf([Data1]=0, IIf([Data2]=0, IIf([Data3]=0,0),1,2),3) As AvgNum,
IIf(AvgNum<>0,Total/AvgNum,0) As AvgTotal
FROM TableName;
 

Mary

[NeoPa]

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.

[abouddan]

Try this ...

SELECT Data1, Data2, Data3, (Data1 + Data2 + Data3) As Total,
IIf([Data1]=0, IIf([Data2]=0, IIf([Data3]=0,0),1,2),3) As AvgNum,
IIf(AvgNum<>0,Total/AvgNum,0) As AvgTotal
FROM TableName;
 

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.

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

[MMcCarthy]

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

[NeoPa]

You can tidy it up a little more as D1 through D3 are not required more than once :

SELECT Data1,Data2,Data3,
       (Data1+Data2+Data3) as Total,
       IIf(Data1=0,0,1)+IIf(Data2=0,0,1)+IIf(Data3=0,0,1) As Avg,
       IIF (Avg=0,0,Total/Avg) as Average)
FROM tablename