conditional average value

[gtslabs]

How can I get the average of a querry result field based on the
condition of another field in the same query?

For instance I need to get average value of all X if Y = Z
I was going to put this into an unbound textbox.
I assume I need vba?

 

[Tom van Stiphout]

On Tue, 21 Oct 2008 19:46:24 -0700 (PDT), gtslabs

You can use VBA, the DAvg function. For example in the Immediate
window you can enter:
?DAvg("x", "YourTable", "Y=Z")

Or you can create a Totals query to essentially do the same.

-Tom.
Microsoft Access MVP

 

[Duane Hookom]

Do you want to do this in a form or report? If so, you should be able create
a control source like:
=Sum(Abs(y=z) * x)/Sum(x)

 

[Allen Browne]

In query design view, type an expression into the Field row like this:
IIf([Y]=[Z], [X], Null)

Depress the Total button on the toolbar.
Access adds a Total row to the grid.
Choose Average under this field.

I'm not sure how you want the average calculated. Perhaps you intend:
IIf([Y]=[Z], [X], IIf([Y] Is Null OR [Z] Is Null, Null, 0))

 

[gtslabs]

In query design view, type an expression into the Field row like this:
    IIf([Y]=[Z], [X], Null)

Depress the Total button on the toolbar.
Access adds a Total row to the grid.
Choose Average under this field.

I'm not sure how you want the average calculated. Perhaps you intend:
    IIf([Y]=[Z], [X], IIf([Y] Is Null OR [Z] Is Null, Null, 0))

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

How can I get the average of a querry result field based on the
condition of another field in the same query?

For instance I need to get average value of all X if Y = Z
I was going to put this into an unbound textbox.
I assume I need vba?- Hide quoted text -

- Show quoted text -

Well I was hoping to use a function in vba but I am not sure how to
reference them or pass arguments.
I have a query like this:

Query Name: Schedule_query

Cycle Stress Cure
7 500
7 600
28 1000
28 900
28 800 onsite
28
56
56

I need to know the average stress at 7, 28 and 56 days.
But some cases there is no value in the stress column either because
it is still pending or it will never be needed.
So I was thinking of something like this in psuedocode:

7_Avg = if(Cycle=7 and Stress<>Null, then get average of Stress where
Cycle=7, else "")
28_Avg = if(Cycle=28 and Stress<>Null and Cure<>onsite, then get
average of Stress where Cycle=28, else "")
56_Avg = if(Cycle=56 and Stress<>Null, then get average of Stress
where Cycle=56, else "")

So I was thinking this is too complicated and needed vba function.
I also need this to be stored in the querry it pulls the data from so
I can pull it in form excel with the rest of the data.

 

[Allen Browne]

Create a query

Depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

In the total row under Cycle and Cure, accept Group By.
Under Stress, choose Average.

Add any criteria you need to exclude rows that should be totally omitted.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.
-----original post-----------
Well I was hoping to use a function in vba but I am not sure how to
reference them or pass arguments.
I have a query like this:

Query Name: Schedule_query

Cycle Stress Cure
7 500
7 600
28 1000
28 900
28 800 onsite
28
56
56

I need to know the average stress at 7, 28 and 56 days.
But some cases there is no value in the stress column either because
it is still pending or it will never be needed.
So I was thinking of something like this in psuedocode:

7_Avg = if(Cycle=7 and Stress<>Null, then get average of Stress where
Cycle=7, else "")
28_Avg = if(Cycle=28 and Stress<>Null and Cure<>onsite, then get
average of Stress where Cycle=28, else "")
56_Avg = if(Cycle=56 and Stress<>Null, then get average of Stress
where Cycle=56, else "")

So I was thinking this is too complicated and needed vba function.
I also need this to be stored in the querry it pulls the data from so
I can pull it in form excel with the rest of the data.

 

[gtslabs]

Create a query

Depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

In the total row under Cycle and Cure, accept Group By.
Under Stress, choose Average.

Add any criteria you need to exclude rows that should be totally omitted.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
-----original post-----------
Well I was hoping to use a function in vba but I am not sure how to
reference them or pass arguments.
I have a query like this:

Query Name: Schedule_query

Cycle    Stress   Cure
7           500
7           600
28         1000
28         900
28         800       onsite
28
56
56

I need to know the average stress at 7, 28 and 56 days.
But some cases there is no value in the stress column either because
it is still pending or it will never be needed.
So I was thinking of something like this in psuedocode:

7_Avg = if(Cycle=7 and Stress<>Null, then get average of Stress where
Cycle=7, else "")
28_Avg = if(Cycle=28 and Stress<>Null and Cure<>onsite, then get
average of Stress where Cycle=28, else "")
56_Avg = if(Cycle=56 and Stress<>Null, then get average of Stress
where Cycle=56, else "")

So I was thinking this is too complicated and needed vba function.
I also need this to be stored in the querry it pulls the data from so
I can pull it in form excel with the rest of the data.

I get an error message even when they are all set to "Group By" or
changing the one to Avg
"You tried to execute a query that does not include the specified
Expression (See Below)
as part of an aggregate function.

And the expression is actually showing the below lines substituted
here as Exp5/Exp3 in the error message

IIf(log!Mold="Prisim",Schedule!Diameter_1*Schedule!Diameter_2,
(((Schedule!Diameter_1+Schedule!Diameter_2)/2)*((Schedule!
Diameter_1+Schedule!Diameter_2)/2)*3.1415/4))) AS Expr3

Schedule.[Break, lbs], Round((Schedule![Break, lbs]/[Expr3])/10)*10 AS
Expr5

What a I doing wrong?

 

[gtslabs]

Create a query

Depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

In the total row under Cycle and Cure, accept Group By.
Under Stress, choose Average.

Add any criteria you need to exclude rows that should be totally omitted.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
-----original post-----------
Well I was hoping to use a function in vba but I am not sure how to
reference them or pass arguments.
I have a query like this:

Query Name: Schedule_query

Cycle    Stress   Cure
7           500
7           600
28         1000
28         900
28         800       onsite
28
56
56

I need to know the average stress at 7, 28 and 56 days.
But some cases there is no value in the stress column either because
it is still pending or it will never be needed.
So I was thinking of something like this in psuedocode:

7_Avg = if(Cycle=7 and Stress<>Null, then get average of Stress where
Cycle=7, else "")
28_Avg = if(Cycle=28 and Stress<>Null and Cure<>onsite, then get
average of Stress where Cycle=28, else "")
56_Avg = if(Cycle=56 and Stress<>Null, then get average of Stress
where Cycle=56, else "")

So I was thinking this is too complicated and needed vba function.
I also need this to be stored in the querry it pulls the data from so
I can pull it in form excel with the rest of the data.

I get an error message even when they are all set to "Group By" or
changing the one to Avg
"You tried to execute a query that does not include the specified
Expression (See Below)
as part of an aggregate function.

And the expression is actually showing the below lines substituted
here as Exp5/Exp3 in the error message


IIf(log!Mold="Prisim",Schedule!Diameter_1*Schedule!Diameter_2,
(((Schedule!Diameter_1+Schedule!Diameter_2)/2)*((Schedule!
Diameter_1+Schedule!Diameter_2)/2)*3.1415/4))) AS Expr3


Round((Schedule![Break, lbs]/[Expr3])/10)*10 AS
Expr5


What a I doing wrong?

 

[Allen Browne]

Access is complaining that it cannot find all the elements it needs to work
with *after* the grouping has been performed. For example, your expression
is depending on log!Mold, and if that's not there in the query output, it
won't be able to group on it.

Try breaking this down into 2 queries:
- Get the expression working in a query with no grouping.
- Then use that query as an input 'table' for the one where you perform the
grouping.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Create a query

Depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

In the total row under Cycle and Cure, accept Group By.
Under Stress, choose Average.

Add any criteria you need to exclude rows that should be totally omitted.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
-----original post-----------
Well I was hoping to use a function in vba but I am not sure how to
reference them or pass arguments.
I have a query like this:

Query Name: Schedule_query

Cycle Stress Cure
7 500
7 600
28 1000
28 900
28 800 onsite
28
56
56

I need to know the average stress at 7, 28 and 56 days.
But some cases there is no value in the stress column either because
it is still pending or it will never be needed.
So I was thinking of something like this in psuedocode:

7_Avg = if(Cycle=7 and Stress<>Null, then get average of Stress where
Cycle=7, else "")
28_Avg = if(Cycle=28 and Stress<>Null and Cure<>onsite, then get
average of Stress where Cycle=28, else "")
56_Avg = if(Cycle=56 and Stress<>Null, then get average of Stress
where Cycle=56, else "")

So I was thinking this is too complicated and needed vba function.
I also need this to be stored in the querry it pulls the data from so
I can pull it in form excel with the rest of the data.

I get an error message even when they are all set to "Group By" or
changing the one to Avg
"You tried to execute a query that does not include the specified
Expression (See Below)
as part of an aggregate function.

And the expression is actually showing the below lines substituted
here as Exp5/Exp3 in the error message

IIf(log!Mold="Prisim",Schedule!Diameter_1*Schedule!Diameter_2,
(((Schedule!Diameter_1+Schedule!Diameter_2)/2)*((Schedule!
Diameter_1+Schedule!Diameter_2)/2)*3.1415/4))) AS Expr3

Schedule.[Break, lbs], Round((Schedule![Break, lbs]/[Expr3])/10)*10 AS
Expr5

What a I doing wrong?

 

[Paul Shapiro]

If you do a Group By query, the avg aggregate function ignores null values.
That sounds like what you want. Something like:
Select Cycle, avg(Stress) AS AverageStress, count(Stress) as
ObservationCount
From Schedule_query
Where Cycle In (7, 28, 56)
Group By Cycle