Conditional Average in Query

[Jay]

Hi,

I have a table with a number of numerical fields. Each of these fields has a
related checkbox field. What I want to do in the query is average the
numerical fields if the related check box is ticked.

So, taking two fields called CheckBoxPrice & Price, in design view, what do
I put in the Criteria row of the Price column so that it only averages the
records where there the CheckBox field is ticked. I know the numerical value
for a check is -1, but I'm not sure of the criteria expression, is it IF or
WHERE Checkbox=-1? Or something like that.

Help greatly appreciated.

Jay
___

 

[Guest]

put this in the criteria row for your check boxes.
[Forms]![MyFormName]![MyCheckBoxName = -1

 

[Jay]

Sorry my mistake - what I *meant* to say was I have a table with several
numerical fields, each of which has a related Yes/No field in the same table
(which is input via a check box on the form).

Table Name Field Name Type
__________________________________
TblPrices Price1 Number
TblPrices CheckPrice Yes/No (with a related check box on input form)

So, in the Query, I'm wanting to Average Price1 if the CheckPrice field in
the same table = Yes (-1?). So I'm wanting to check the table field, not the
form. My mistake, I know.

So do I just put a statement in the Criteria Row for Price1 indicating to do
the Average where/if CheckPrice=-1. I've tried all ways of typing it & can't
get it?

Cheers

J

put this in the criteria row for your check boxes.
[Forms]![MyFormName]![MyCheckBoxName = -1

Hi,

I have a table with a number of numerical fields. Each of these fields has a
related checkbox field. What I want to do in the query is average the
numerical fields if the related check box is ticked.

So, taking two fields called CheckBoxPrice & Price, in design view, what do
I put in the Criteria row of the Price column so that it only averages the
records where there the CheckBox field is ticked. I know the numerical value
for a check is -1, but I'm not sure of the criteria expression, is it IF or
WHERE Checkbox=-1? Or something like that.

Help greatly appreciated.

Jay
___

 

[Guest]

Oops, my mistake. The criteria just needs to be in the column for the check
box field.
[CheckPrice] = -1

Same for each of those fields.

Sorry my mistake - what I *meant* to say was I have a table with several
numerical fields, each of which has a related Yes/No field in the same table
(which is input via a check box on the form).

Table Name Field Name Type
__________________________________
TblPrices Price1 Number
TblPrices CheckPrice Yes/No (with a related check box on input form)

So, in the Query, I'm wanting to Average Price1 if the CheckPrice field in
the same table = Yes (-1?). So I'm wanting to check the table field, not the
form. My mistake, I know.

So do I just put a statement in the Criteria Row for Price1 indicating to do
the Average where/if CheckPrice=-1. I've tried all ways of typing it & can't
get it?

Cheers

J

put this in the criteria row for your check boxes.
[Forms]![MyFormName]![MyCheckBoxName = -1

Hi,

I have a table with a number of numerical fields. Each of these fields has a
related checkbox field. What I want to do in the query is average the
numerical fields if the related check box is ticked.

So, taking two fields called CheckBoxPrice & Price, in design view, what do
I put in the Criteria row of the Price column so that it only averages the
records where there the CheckBox field is ticked. I know the numerical value
for a check is -1, but I'm not sure of the criteria expression, is it IF or
WHERE Checkbox=-1? Or something like that.

Help greatly appreciated.

Jay
___

 

[Jay]

Yes, I tried that, but because I am trying to do the same to about 7 fields,
it only averages the fields where there is a -1 in *all* the Check fields?
So even if there are 10 records where Price1 has a corresponding -1 in the
CheckPrice field, it will only average the records where all the other Check
fields are -1 as well?

The prices are for Car Options - CD, Sat Nav etc. and the checkbox field
basically signifies whether the price is significant enough to be used for
our purposes. So that's why I only want to average the checkbox=-1.

The way below only averages where *all* checkboxes are -1?

I'm stumped & am thinking it must be pretty simple. (Personally I'd have
never had a yes/no field in the table & wouldn't have bothered even
capturing insignificant values).

Any ideas?
J

Oops, my mistake. The criteria just needs to be in the column for the check
box field.
[CheckPrice] = -1

Same for each of those fields.

Sorry my mistake - what I *meant* to say was I have a table with several
numerical fields, each of which has a related Yes/No field in the same table
(which is input via a check box on the form).

Table Name Field Name Type
__________________________________
TblPrices Price1 Number
TblPrices CheckPrice Yes/No (with a related check box on input form)

So, in the Query, I'm wanting to Average Price1 if the CheckPrice field in
the same table = Yes (-1?). So I'm wanting to check the table field, not the
form. My mistake, I know.

So do I just put a statement in the Criteria Row for Price1 indicating to do
the Average where/if CheckPrice=-1. I've tried all ways of typing it & can't
get it?

Cheers

J

put this in the criteria row for your check boxes.
[Forms]![MyFormName]![MyCheckBoxName = -1

:

Hi,

I have a table with a number of numerical fields. Each of these fields has
a
related checkbox field. What I want to do in the query is average the
numerical fields if the related check box is ticked.

So, taking two fields called CheckBoxPrice & Price, in design view, what do
I put in the Criteria row of the Price column so that it only averages the
records where there the CheckBox field is ticked. I know the numerical
value
for a check is -1, but I'm not sure of the criteria expression, is it IF or
WHERE Checkbox=-1? Or something like that.

Help greatly appreciated.

Jay
___

 

[John Spencer]

Perhaps something like

SELECT Avg(IIF(CheckCDPrice=True, CD,Null) as AvgCDCost,
, Avg(IIF(CheckNavPrice=True, Nav,Null) as AvgNavCost
FROM YourTable

Yes, I tried that, but because I am trying to do the same to about 7
fields,
it only averages the fields where there is a -1 in *all* the Check fields?
So even if there are 10 records where Price1 has a corresponding -1 in the
CheckPrice field, it will only average the records where all the other
Check
fields are -1 as well?

The prices are for Car Options - CD, Sat Nav etc. and the checkbox field
basically signifies whether the price is significant enough to be used for
our purposes. So that's why I only want to average the checkbox=-1.

The way below only averages where *all* checkboxes are -1?

I'm stumped & am thinking it must be pretty simple. (Personally I'd have
never had a yes/no field in the table & wouldn't have bothered even
capturing insignificant values).

Any ideas?
J

Oops, my mistake. The criteria just needs to be in the column for the
check
box field.
[CheckPrice] = -1

Same for each of those fields.

Sorry my mistake - what I *meant* to say was I have a table with several
numerical fields, each of which has a related Yes/No field in the same
table
(which is input via a check box on the form).

Table Name Field Name Type
__________________________________
TblPrices Price1 Number
TblPrices CheckPrice Yes/No (with a related check box on input
form)

So, in the Query, I'm wanting to Average Price1 if the CheckPrice field
in
the same table = Yes (-1?). So I'm wanting to check the table field, not
the
form. My mistake, I know.

So do I just put a statement in the Criteria Row for Price1 indicating
to do
the Average where/if CheckPrice=-1. I've tried all ways of typing it &
can't
get it?

Cheers

J


On 19/5/06 19:58, in article
(e-mail address removed), "Klatuu"

put this in the criteria row for your check boxes.
[Forms]![MyFormName]![MyCheckBoxName = -1

:

Hi,

I have a table with a number of numerical fields. Each of these fields
has
a
related checkbox field. What I want to do in the query is average the
numerical fields if the related check box is ticked.

So, taking two fields called CheckBoxPrice & Price, in design view,
what do
I put in the Criteria row of the Price column so that it only averages
the
records where there the CheckBox field is ticked. I know the numerical
value
for a check is -1, but I'm not sure of the criteria expression, is it
IF or
WHERE Checkbox=-1? Or something like that.

Help greatly appreciated.

Jay
___

 

[Guest]

oh yea, I wasn't paying close enough attention. Well, I can't test it right
now, but here is an idea. I'm not sure if it will work as I descibe it here,
but a bit of tinkering may get it.
instead of using the fields, create one calculated field that give a 0 if
not checked and the value if checked:

ThePrice: IIf([CheckPrice],[Price1],0)

Then make it a totals query and use Average for these columns

Yes, I tried that, but because I am trying to do the same to about 7 fields,
it only averages the fields where there is a -1 in *all* the Check fields?
So even if there are 10 records where Price1 has a corresponding -1 in the
CheckPrice field, it will only average the records where all the other Check
fields are -1 as well?

The prices are for Car Options - CD, Sat Nav etc. and the checkbox field
basically signifies whether the price is significant enough to be used for
our purposes. So that's why I only want to average the checkbox=-1.

The way below only averages where *all* checkboxes are -1?

I'm stumped & am thinking it must be pretty simple. (Personally I'd have
never had a yes/no field in the table & wouldn't have bothered even
capturing insignificant values).

Any ideas?
J

Oops, my mistake. The criteria just needs to be in the column for the check
box field.
[CheckPrice] = -1

Same for each of those fields.

Sorry my mistake - what I *meant* to say was I have a table with several
numerical fields, each of which has a related Yes/No field in the same table
(which is input via a check box on the form).

Table Name Field Name Type
__________________________________
TblPrices Price1 Number
TblPrices CheckPrice Yes/No (with a related check box on input form)

So, in the Query, I'm wanting to Average Price1 if the CheckPrice field in
the same table = Yes (-1?). So I'm wanting to check the table field, not the
form. My mistake, I know.

So do I just put a statement in the Criteria Row for Price1 indicating to do
the Average where/if CheckPrice=-1. I've tried all ways of typing it & can't
get it?

Cheers

J


On 19/5/06 19:58, in article
(e-mail address removed), "Klatuu"

put this in the criteria row for your check boxes.
[Forms]![MyFormName]![MyCheckBoxName = -1

:

Hi,

I have a table with a number of numerical fields. Each of these fields has
a
related checkbox field. What I want to do in the query is average the
numerical fields if the related check box is ticked.

So, taking two fields called CheckBoxPrice & Price, in design view, what do
I put in the Criteria row of the Price column so that it only averages the
records where there the CheckBox field is ticked. I know the numerical
value
for a check is -1, but I'm not sure of the criteria expression, is it IF or
WHERE Checkbox=-1? Or something like that.

Help greatly appreciated.

Jay
___

 

[Jay]

Thanks for the suggestion John, but I'm afraid I'm quite a newbie & haven't
delved much further than design view. Where do I need to put the Select
statement. I don't mean to be a pain

J

Perhaps something like

SELECT Avg(IIF(CheckCDPrice=True, CD,Null) as AvgCDCost,
, Avg(IIF(CheckNavPrice=True, Nav,Null) as AvgNavCost
FROM YourTable

Yes, I tried that, but because I am trying to do the same to about 7
fields,
it only averages the fields where there is a -1 in *all* the Check fields?
So even if there are 10 records where Price1 has a corresponding -1 in the
CheckPrice field, it will only average the records where all the other
Check
fields are -1 as well?

The prices are for Car Options - CD, Sat Nav etc. and the checkbox field
basically signifies whether the price is significant enough to be used for
our purposes. So that's why I only want to average the checkbox=-1.

The way below only averages where *all* checkboxes are -1?

I'm stumped & am thinking it must be pretty simple. (Personally I'd have
never had a yes/no field in the table & wouldn't have bothered even
capturing insignificant values).

Any ideas?
J

Oops, my mistake. The criteria just needs to be in the column for the
check
box field.
[CheckPrice] = -1

Same for each of those fields.

:

Sorry my mistake - what I *meant* to say was I have a table with several
numerical fields, each of which has a related Yes/No field in the same
table
(which is input via a check box on the form).

Table Name Field Name Type
__________________________________
TblPrices Price1 Number
TblPrices CheckPrice Yes/No (with a related check box on input
form)

So, in the Query, I'm wanting to Average Price1 if the CheckPrice field
in
the same table = Yes (-1?). So I'm wanting to check the table field, not
the
form. My mistake, I know.

So do I just put a statement in the Criteria Row for Price1 indicating
to do
the Average where/if CheckPrice=-1. I've tried all ways of typing it &
can't
get it?

Cheers

J


On 19/5/06 19:58, in article
(e-mail address removed), "Klatuu"

put this in the criteria row for your check boxes.
[Forms]![MyFormName]![MyCheckBoxName = -1

:

Hi,

I have a table with a number of numerical fields. Each of these fields
has
a
related checkbox field. What I want to do in the query is average the
numerical fields if the related check box is ticked.

So, taking two fields called CheckBoxPrice & Price, in design view,
what do
I put in the Criteria row of the Price column so that it only averages
the
records where there the CheckBox field is ticked. I know the numerical
value
for a check is -1, but I'm not sure of the criteria expression, is it
IF or
WHERE Checkbox=-1? Or something like that.

Help greatly appreciated.

Jay
___

 

[Jay]

Klatuu,

I've tried not using the fields and using a calculated field as suggested. I
tried both the following:

CDAverage: Avg(IIf([CheckPrice]=-1,[Price1],0)) with Expression in Total.

CDAverage: IIf([CheckPrice]=-1,[Price1],0)) with Average in Total.

I am also grouping by a Manufacturer field. To give an example - I know
there are 6 records for BMW, 2 of which have a -1 in the CheckPrice field -
meaining the average should be only based on those 2 values.

However from the average value returned by both the above it is clear that
it is using *all* 6 values for the average when it should only be using the
2 which have a -1 CheckPrice value?

Surely there must be a simple(ish) way of averaging only those values? I
can't understaind why the above IIFs don't do it.

Any ideas? (I've really appreciated your help with this)

J

oh yea, I wasn't paying close enough attention. Well, I can't test it right
now, but here is an idea. I'm not sure if it will work as I descibe it here,
but a bit of tinkering may get it.
instead of using the fields, create one calculated field that give a 0 if
not checked and the value if checked:

ThePrice: IIf([CheckPrice],[Price1],0)

Then make it a totals query and use Average for these columns

Yes, I tried that, but because I am trying to do the same to about 7 fields,
it only averages the fields where there is a -1 in *all* the Check fields?
So even if there are 10 records where Price1 has a corresponding -1 in the
CheckPrice field, it will only average the records where all the other Check
fields are -1 as well?

The prices are for Car Options - CD, Sat Nav etc. and the checkbox field
basically signifies whether the price is significant enough to be used for
our purposes. So that's why I only want to average the checkbox=-1.

The way below only averages where *all* checkboxes are -1?

I'm stumped & am thinking it must be pretty simple. (Personally I'd have
never had a yes/no field in the table & wouldn't have bothered even
capturing insignificant values).

Any ideas?
J

Oops, my mistake. The criteria just needs to be in the column for the check
box field.
[CheckPrice] = -1

Same for each of those fields.

:

Sorry my mistake - what I *meant* to say was I have a table with several
numerical fields, each of which has a related Yes/No field in the same
table
(which is input via a check box on the form).

Table Name Field Name Type
__________________________________
TblPrices Price1 Number
TblPrices CheckPrice Yes/No (with a related check box on input form)

So, in the Query, I'm wanting to Average Price1 if the CheckPrice field in
the same table = Yes (-1?). So I'm wanting to check the table field, not
the
form. My mistake, I know.

So do I just put a statement in the Criteria Row for Price1 indicating to
do
the Average where/if CheckPrice=-1. I've tried all ways of typing it &
can't
get it?

Cheers

J


On 19/5/06 19:58, in article
(e-mail address removed), "Klatuu"

put this in the criteria row for your check boxes.
[Forms]![MyFormName]![MyCheckBoxName = -1

:

Hi,

I have a table with a number of numerical fields. Each of these fields
has
a
related checkbox field. What I want to do in the query is average the
numerical fields if the related check box is ticked.

So, taking two fields called CheckBoxPrice & Price, in design view, what
do
I put in the Criteria row of the Price column so that it only averages
the
records where there the CheckBox field is ticked. I know the numerical
value
for a check is -1, but I'm not sure of the criteria expression, is it IF
or
WHERE Checkbox=-1? Or something like that.

Help greatly appreciated.

Jay
___

 

[John Spencer]

Replace the zero with null

Avg(IIf([CheckPrice]=-1,[Price1],Null))

Average will ignore nulls.

Klatuu,

I've tried not using the fields and using a calculated field as suggested. I
tried both the following:

CDAverage: Avg(IIf([CheckPrice]=-1,[Price1],0)) with Expression in Total.

CDAverage: IIf([CheckPrice]=-1,[Price1],0)) with Average in Total.

I am also grouping by a Manufacturer field. To give an example - I know
there are 6 records for BMW, 2 of which have a -1 in the CheckPrice field -
meaining the average should be only based on those 2 values.

However from the average value returned by both the above it is clear that
it is using *all* 6 values for the average when it should only be using the
2 which have a -1 CheckPrice value?

Surely there must be a simple(ish) way of averaging only those values? I
can't understaind why the above IIFs don't do it.

Any ideas? (I've really appreciated your help with this)

J

oh yea, I wasn't paying close enough attention. Well, I can't test it right
now, but here is an idea. I'm not sure if it will work as I descibe it here,
but a bit of tinkering may get it.
instead of using the fields, create one calculated field that give a 0 if
not checked and the value if checked:

ThePrice: IIf([CheckPrice],[Price1],0)

Then make it a totals query and use Average for these columns

Yes, I tried that, but because I am trying to do the same to about 7 fields,
it only averages the fields where there is a -1 in *all* the Check fields?
So even if there are 10 records where Price1 has a corresponding -1 in the
CheckPrice field, it will only average the records where all the other Check
fields are -1 as well?

The prices are for Car Options - CD, Sat Nav etc. and the checkbox field
basically signifies whether the price is significant enough to be used for
our purposes. So that's why I only want to average the checkbox=-1.

The way below only averages where *all* checkboxes are -1?

I'm stumped & am thinking it must be pretty simple. (Personally I'd have
never had a yes/no field in the table & wouldn't have bothered even
capturing insignificant values).

Any ideas?
J


On 19/5/06 20:52, in article
(e-mail address removed), "Klatuu"

Oops, my mistake. The criteria just needs to be in the column for the check
box field.
[CheckPrice] = -1

Same for each of those fields.

:

Sorry my mistake - what I *meant* to say was I have a table with several
numerical fields, each of which has a related Yes/No field in the same
table
(which is input via a check box on the form).

Table Name Field Name Type
__________________________________
TblPrices Price1 Number
TblPrices CheckPrice Yes/No (with a related check box on input form)

So, in the Query, I'm wanting to Average Price1 if the CheckPrice field in
the same table = Yes (-1?). So I'm wanting to check the table field, not
the
form. My mistake, I know.

So do I just put a statement in the Criteria Row for Price1 indicating to
do
the Average where/if CheckPrice=-1. I've tried all ways of typing it &
can't
get it?

Cheers

J


On 19/5/06 19:58, in article
(e-mail address removed), "Klatuu"

put this in the criteria row for your check boxes.
[Forms]![MyFormName]![MyCheckBoxName = -1

:

Hi,

I have a table with a number of numerical fields. Each of these fields
has
a
related checkbox field. What I want to do in the query is average the
numerical fields if the related check box is ticked.

So, taking two fields called CheckBoxPrice & Price, in design view, what
do
I put in the Criteria row of the Price column so that it only averages
the
records where there the CheckBox field is ticked. I know the numerical
value
for a check is -1, but I'm not sure of the criteria expression, is it IF
or
WHERE Checkbox=-1? Or something like that.

Help greatly appreciated.

Jay
___

 

[Jay]

Fantastic! Thanks a lot John.

D'you mind me asking exactly what the Null's purpose is? My (very probably
flawed understanding of the statement is that the IIF checks whether the
CheckPrice field meets the requred criteria(-1) and then uses the related
Price1 fields from the same record in the Average calculation. Does the IIF
require an 'else' clause (like in XL) & if this is the case why else Null?

And how did having a zero instead of null lead to that outcome?

Is the statement saying that if CheckPrice is not -1, then not to include
the related Price1 value in the calculation.

And how did having a zero instead of the Null lead to the Avg being based on
all CheckPrice records (regardless of value)?

Sorry to be a pain but I'm on a steep learning curve with Access & this is
really helping a lot.

-J-

Replace the zero with null

Avg(IIf([CheckPrice]=-1,[Price1],Null))

Average will ignore nulls.

Klatuu,

I've tried not using the fields and using a calculated field as suggested. I
tried both the following:

CDAverage: Avg(IIf([CheckPrice]=-1,[Price1],0)) with Expression in Total.

CDAverage: IIf([CheckPrice]=-1,[Price1],0)) with Average in Total.

I am also grouping by a Manufacturer field. To give an example - I know
there are 6 records for BMW, 2 of which have a -1 in the CheckPrice field -
meaining the average should be only based on those 2 values.

However from the average value returned by both the above it is clear that
it is using *all* 6 values for the average when it should only be using the
2 which have a -1 CheckPrice value?

Surely there must be a simple(ish) way of averaging only those values? I
can't understaind why the above IIFs don't do it.

Any ideas? (I've really appreciated your help with this)

J

oh yea, I wasn't paying close enough attention. Well, I can't test it right
now, but here is an idea. I'm not sure if it will work as I descibe it here,
but a bit of tinkering may get it.
instead of using the fields, create one calculated field that give a 0 if
not checked and the value if checked:

ThePrice: IIf([CheckPrice],[Price1],0)

Then make it a totals query and use Average for these columns

:

Yes, I tried that, but because I am trying to do the same to about 7
fields,
it only averages the fields where there is a -1 in *all* the Check fields?
So even if there are 10 records where Price1 has a corresponding -1 in the
CheckPrice field, it will only average the records where all the other
Check
fields are -1 as well?

The prices are for Car Options - CD, Sat Nav etc. and the checkbox field
basically signifies whether the price is significant enough to be used for
our purposes. So that's why I only want to average the checkbox=-1.

The way below only averages where *all* checkboxes are -1?

I'm stumped & am thinking it must be pretty simple. (Personally I'd have
never had a yes/no field in the table & wouldn't have bothered even
capturing insignificant values).

Any ideas?
J


On 19/5/06 20:52, in article
(e-mail address removed), "Klatuu"

Oops, my mistake. The criteria just needs to be in the column for the
check
box field.
[CheckPrice] = -1

Same for each of those fields.

:

Sorry my mistake - what I *meant* to say was I have a table with several
numerical fields, each of which has a related Yes/No field in the same
table
(which is input via a check box on the form).

Table Name Field Name Type
__________________________________
TblPrices Price1 Number
TblPrices CheckPrice Yes/No (with a related check box on input
form)

So, in the Query, I'm wanting to Average Price1 if the CheckPrice field
in
the same table = Yes (-1?). So I'm wanting to check the table field, not
the
form. My mistake, I know.

So do I just put a statement in the Criteria Row for Price1 indicating to
do
the Average where/if CheckPrice=-1. I've tried all ways of typing it &
can't
get it?

Cheers

J


On 19/5/06 19:58, in article
(e-mail address removed), "Klatuu"

put this in the criteria row for your check boxes.
[Forms]![MyFormName]![MyCheckBoxName = -1

:

Hi,

I have a table with a number of numerical fields. Each of these fields
has
a
related checkbox field. What I want to do in the query is average the
numerical fields if the related check box is ticked.

So, taking two fields called CheckBoxPrice & Price, in design view,
what
do
I put in the Criteria row of the Price column so that it only averages
the
records where there the CheckBox field is ticked. I know the numerical
value
for a check is -1, but I'm not sure of the criteria expression, is it
IF
or
WHERE Checkbox=-1? Or something like that.

Help greatly appreciated.

Jay
___

 

[John Spencer]

The aggregate functions (Avg, Min, Max, etc) ignore null values.

If you leave the third argument out of the IIF statement, I'm not sure what
value will be returned in an SQL statement - probably null, but why gamble. In
VBA the third argument is required.

6 records with values, 0;0;1;2;3;6
Divide 12/6 = 2
6 records with values Null; Null; 1;2;3;6
Devide 12/4 = 3

Fantastic! Thanks a lot John.

D'you mind me asking exactly what the Null's purpose is? My (very probably
flawed understanding of the statement is that the IIF checks whether the
CheckPrice field meets the requred criteria(-1) and then uses the related
Price1 fields from the same record in the Average calculation. Does the IIF
require an 'else' clause (like in XL) & if this is the case why else Null?

And how did having a zero instead of null lead to that outcome?

Is the statement saying that if CheckPrice is not -1, then not to include
the related Price1 value in the calculation.

And how did having a zero instead of the Null lead to the Avg being based on
all CheckPrice records (regardless of value)?

Sorry to be a pain but I'm on a steep learning curve with Access & this is
really helping a lot.

-J-

Replace the zero with null

Avg(IIf([CheckPrice]=-1,[Price1],Null))

Average will ignore nulls.

Klatuu,

I've tried not using the fields and using a calculated field as suggested. I
tried both the following:

CDAverage: Avg(IIf([CheckPrice]=-1,[Price1],0)) with Expression in Total.

CDAverage: IIf([CheckPrice]=-1,[Price1],0)) with Average in Total.

I am also grouping by a Manufacturer field. To give an example - I know
there are 6 records for BMW, 2 of which have a -1 in the CheckPrice field -
meaining the average should be only based on those 2 values.

However from the average value returned by both the above it is clear that
it is using *all* 6 values for the average when it should only be using the
2 which have a -1 CheckPrice value?

Surely there must be a simple(ish) way of averaging only those values? I
can't understaind why the above IIFs don't do it.

Any ideas? (I've really appreciated your help with this)

J

On 19/5/06 21:34, in article
(e-mail address removed), "Klatuu"

oh yea, I wasn't paying close enough attention. Well, I can't test it right
now, but here is an idea. I'm not sure if it will work as I descibe it here,
but a bit of tinkering may get it.
instead of using the fields, create one calculated field that give a 0 if
not checked and the value if checked:

ThePrice: IIf([CheckPrice],[Price1],0)

Then make it a totals query and use Average for these columns

:

Yes, I tried that, but because I am trying to do the same to about 7
fields,
it only averages the fields where there is a -1 in *all* the Check fields?
So even if there are 10 records where Price1 has a corresponding -1 in the
CheckPrice field, it will only average the records where all the other
Check
fields are -1 as well?

The prices are for Car Options - CD, Sat Nav etc. and the checkbox field
basically signifies whether the price is significant enough to be used for
our purposes. So that's why I only want to average the checkbox=-1.

The way below only averages where *all* checkboxes are -1?

I'm stumped & am thinking it must be pretty simple. (Personally I'd have
never had a yes/no field in the table & wouldn't have bothered even
capturing insignificant values).

Any ideas?
J


On 19/5/06 20:52, in article
(e-mail address removed), "Klatuu"

Oops, my mistake. The criteria just needs to be in the column for the
check
box field.
[CheckPrice] = -1

Same for each of those fields.

:

Sorry my mistake - what I *meant* to say was I have a table with several
numerical fields, each of which has a related Yes/No field in the same
table
(which is input via a check box on the form).

Table Name Field Name Type
__________________________________
TblPrices Price1 Number
TblPrices CheckPrice Yes/No (with a related check box on input
form)

So, in the Query, I'm wanting to Average Price1 if the CheckPrice field
in
the same table = Yes (-1?). So I'm wanting to check the table field, not
the
form. My mistake, I know.

So do I just put a statement in the Criteria Row for Price1 indicating to
do
the Average where/if CheckPrice=-1. I've tried all ways of typing it &
can't
get it?

Cheers

J


On 19/5/06 19:58, in article
(e-mail address removed), "Klatuu"

put this in the criteria row for your check boxes.
[Forms]![MyFormName]![MyCheckBoxName = -1

:

Hi,

I have a table with a number of numerical fields. Each of these fields
has
a
related checkbox field. What I want to do in the query is average the
numerical fields if the related check box is ticked.

So, taking two fields called CheckBoxPrice & Price, in design view,
what
do
I put in the Criteria row of the Price column so that it only averages
the
records where there the CheckBox field is ticked. I know the numerical
value
for a check is -1, but I'm not sure of the criteria expression, is it
IF
or
WHERE Checkbox=-1? Or something like that.

Help greatly appreciated.

Jay
___