averaging numbers in linked tables using cal query 1

[Tessadear]

how can I use the average function to average numbers from various linked tables in a custom calulated query field.Access allows me to add the numbers but if I needed to average those numbers how would I do that.


eg.([table1]![number1]([table2]![number2]+[table3]![number3]

I need to calculate the average of the 3 numbers in the calculated query.

 

[Remou]

I think the answer is 'it depends'. This may suit:

[tt]Percentage: (([tblA].[Field1]+[tblB].[Field1]+[tblC].[Field1])/(DSum("Field1","tblA")+DSum("Field1","tblB")+DSum("Field1","tblC")))*100[/tt]

 

[Tessadear]

I am not getting the correct answer using this formula

Lets say I had 20 in table1, 50 in table2 and 60 in table3

what i need is to find the average of those 3 numbers. when I work it out using the formula above I am getting an incorrect answer fot the average.

Percentage: (([table1].[Amount]+[table2].[Amount]+[table3].[Amount])/(Dsum("Amount","table1")+DSum("Amount","table2")+DSum("Amount","table3")))*100

the answer is coming up as 52.4 when it should be 43.3

 

[Remou]

That is much easier. It seemed to me from your question that you wanted the average for the three tables, not the three rows. I then wandered into percentages for some reason.

[tt]Average: ([table1].[Amount]+[table2].[Amount]+[table3].[Amount])/3[/tt]

 

[Tessadear]

I figured I could do it that way but I was trying to get rid of the / by the number of numbers because for example in some instances there might only be 2 numbers instead of 3.....I wonder if I could do a count on the number of values entered for example if there are 2 numbers the count would divide by 2 and if there are 3 numbers the count would divide by 3. When only 2 number exist the average would be wrong since it divides by 3

 

[Remou]

How about:
[tt]([table1].[Amount]+[table2].[Amount]+[table3].[Amount])/(IIf(Nz([tblb].[field1],0)>0,1,0)+IIf(Nz([tblb].[field1],0)>0,1,0)+IIf(Nz([tblc].[field1],0)>0,1,0))[/tt]

 

[Tessadear]

([table1].[Amount]+[table2].[Amount]+[table3].[Amount])/(IIf(Nz([tblb].[field1],0)>0,1,0)+IIf(Nz([tblb].[field1],0)>0,1,0)+IIf(Nz([tblc].[field1],0)>0,1,0))

this code works, but if an amount is missing the average is not displayed. I tired using the Nz before each table in the first part of the code but that did not work. what am I doing wrong.

 

[Remou]

This is neater, as it takes advantage of the fact that you can refer to earlier columns by name and that the Absolute value of True is 1:

[tt]SELECT Val(Nz([tblA].[Field1],0)) AS A,
Val(Nz([tblB].[Field1],0)) AS B,
Val(Nz([tblC].[Field1],0)) AS C,
[A]++[C] AS [Sum],
Abs(([A]>0)+(>0)+([C]>0)) AS Count,
[Sum]/[Count] AS Average
FROM <...>[/tt]

 

[Tessadear]

This is the only code I got to work for me. How can I edit it to display the average when an amount value is blank.

It works well except that when an amount is missing the average is not displayed.



([table1].[Amount]+[table2].[Amount]+[table3].[Amount])/(IIf(Val(Nz([table1].[Amount],0))>0,1,0)+IIf(Val(Nz([table2].[Amount],0))>0,1,0)+IIf(Val(Nz([table3].[Amount],0))>0,1,0))

Did not get the select statement to work

 

[Remou]

If you are using this in a query, have you tried my previous post? All you need to do is to fill in the From bit, which you should be able to cut from the SQL view of your existing query.

 

[PHV]

Perhaps this ?
(Val(Nz([table1].[Amount],0))+Val(Nz([table2].[Amount],0))+Val(Nz([table3].[Amount],0)))/(IIf(Val(Nz([table1].[Amount],0))>0,1,0)+IIf(Val(Nz([table2].[Amount],0))>0,1,0)+IIf(Val(Nz([table3].[Amount],0))>0,1,0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Tessadear]

CODE WORKS LIKE A DREAM!!!!