Totals Query Help

[Jay]

Hi,

I wonder if anyone could point me in the right direction. I am trying to
query someone else's table & can't do what I need to.

The table contains sveral Price fields eg CDPrice, AirConPrice, SatNavPrice
etc. In the same table there are also Yes/No fields for each of these fields
eg. CDCheck, AirConCheck, SatNavCheck etc. The yes/no field basically tells
me whether the Price field value should be included in queries.

So, to use CD as an example. I'm wanting a query that will average CDPrice
if CDCheck is Yes (although it is a check box so will be -1). And I'm
wanting to average all the Price fields in the same query (if the related
Check field= -1).

I can *select* the records where the Check is -1 by putting the following in
the criteria row of the Price (in Query Design View):

[CDCheck]=-1

However, if I try the same in a Totals query, when Avg is specified in the
Total Row I get the following msg: "You tried to execute a query that does
not include the specified expression '[CDCheck]=-1' as part of an aggregate
function.'?

I know it must be pretty simple to do, but it's beyond a newbie like me ,
so any help would be great.

Many thanks

Jason

 

[jleckrone]

In a Totals query, you will need to have a column for CDCheck and one
for CDPrice. In the Total row for CDCheck it should say Group By and
for CDPrice it should be Avg. Put -1 in the criteria for CDCheck.

 

[Jay]

Thanks for the suggestion. That way works fine if the query contains just
one Price field & one Check field, but if I do it on more than one it only
averages the records where *all* the check fields are -1. So even if I know
there are ten Prices for Sat Nav for example (with corresponding -1's) it
will only average the values which have -1's in all the other check fields
(CD, Air con etc.)

J

 

[Marshall Barton]

I wonder if anyone could point me in the right direction. I am trying to
query someone else's table & can't do what I need to.

The table contains sveral Price fields eg CDPrice, AirConPrice, SatNavPrice
etc. In the same table there are also Yes/No fields for each of these fields
eg. CDCheck, AirConCheck, SatNavCheck etc. The yes/no field basically tells
me whether the Price field value should be included in queries.

So, to use CD as an example. I'm wanting a query that will average CDPrice
if CDCheck is Yes (although it is a check box so will be -1). And I'm
wanting to average all the Price fields in the same query (if the related
Check field= -1).

I can *select* the records where the Check is -1 by putting the following in
the criteria row of the Price (in Query Design View):

[CDCheck]=-1

However, if I try the same in a Totals query, when Avg is specified in the
Total Row I get the following msg: "You tried to execute a query that does
not include the specified expression '[CDCheck]=-1' as part of an aggregate
function.'?

If your CDPrice field contained Null when CDCheck is not
checked, you could just Avg the field.

If the CDPrice firld has a 0 or or a junk value when CDCheck
is not checked, then use calculated columns for the sum.

AvgCDPrice: IIf(CDCheck, CDPrice, Null)
and select AVG in the totals row.