average

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to use an IIf statement to calculate the average on the query but
it doesn't seem to work.here's an sql statement for my query:I created a
Union query first and now i wnt to average the values contained in these two
months: July and September

SELECT QAVG.Broker, QAVG.EffMonth, Avg(QAVG.AVERAGE) AS AvgOfAVERAGE,
IIf([effmonth]=#7/1/2004# and #9/1/2004#,Sum([average]),0) AS MYAVERAGE
FROM QAVG
GROUP BY QAVG.Broker, QAVG.EffMonth;
 
Is effective month the date value of the first day of the month? Did you
really mean July and September and not July through September?

To check your effmonth value, you need to use something like:
[effmonth]=#7/1/2004# or [effmonth]= #9/1/2004#
This expression will evaluate to true if effmonth is either of these two
date values. If I am correct, you might be able to use an expression like:

Avg(IIf( [effmonth]=#7/1/2004# or [effmonth]= #9/1/2004#, [Average], Null)

This is just a WAG since you haven't provided any sample records and
expected results from the query.
 
Duane Hookom said:
Is effective month the date value of the first day of the month? Did you
really mean July and September and not July through September?

To check your effmonth value, you need to use something like:
[effmonth]=#7/1/2004# or [effmonth]= #9/1/2004#
This expression will evaluate to true if effmonth is either of these two
date values. If I am correct, you might be able to use an expression like:

Avg(IIf( [effmonth]=#7/1/2004# or [effmonth]= #9/1/2004#, [Average], Null)

This is just a WAG since you haven't provided any sample records and
expected results from the query.
--
Duane Hookom
MS Access MVP
--

Benton said:
I'm trying to use an IIf statement to calculate the average on the query
but
it doesn't seem to work.here's an sql statement for my query:I created a
Union query first and now i wnt to average the values contained in these
two
months: July and September

SELECT QAVG.Broker, QAVG.EffMonth, Avg(QAVG.AVERAGE) AS AvgOfAVERAGE,
IIf([effmonth]=#7/1/2004# and #9/1/2004#,Sum([average]),0) AS MYAVERAGE
FROM QAVG
GROUP BY QAVG.Broker, QAVG.EffMonth;
Click to expand...

Duanne,
Click to expand...
EffMonth is the date of the first value of the month which contains values
for my averages for each month.No it's July through September.My average
should be 3537 if i'm right

I've tried it the your proposed solution and here's is what it gives me:

Broker EffMonth average MYAVERAGE
1000008308 01/09/2003 0
1000012946 01/07/2004 3492 3492
1000012946 01/09/2004 3582 3582
 
Try this syntax. You haven't said if you want this by broker.

SELECT Avg(QAVG.AVERAGE) AS AvgOfAVERAGE,
Avg(IIf( [effmonth] Between #7/1/2004# And #9/1/2004#, [Average], Null) as
Qtr3Avg
FROM QAVG

--
Duane Hookom
MS Access MVP
--

Benton said:
Duane Hookom said:
Is effective month the date value of the first day of the month? Did you
really mean July and September and not July through September?

To check your effmonth value, you need to use something like:
[effmonth]=#7/1/2004# or [effmonth]= #9/1/2004#
This expression will evaluate to true if effmonth is either of these two
date values. If I am correct, you might be able to use an expression
like:

Avg(IIf( [effmonth]=#7/1/2004# or [effmonth]= #9/1/2004#, [Average],
Null)

This is just a WAG since you haven't provided any sample records and
expected results from the query.
--
Duane Hookom
MS Access MVP
--

Benton said:
I'm trying to use an IIf statement to calculate the average on the
query
but
it doesn't seem to work.here's an sql statement for my query:I created
a
Union query first and now i wnt to average the values contained in
these
two
months: July and September

SELECT QAVG.Broker, QAVG.EffMonth, Avg(QAVG.AVERAGE) AS AvgOfAVERAGE,
IIf([effmonth]=#7/1/2004# and #9/1/2004#,Sum([average]),0) AS
MYAVERAGE
FROM QAVG
GROUP BY QAVG.Broker, QAVG.EffMonth;
Click to expand...

Duanne,
Click to expand...
EffMonth is the date of the first value of the month which contains values
for my averages for each month.No it's July through September.My average
should be 3537 if i'm right

I've tried it the your proposed solution and here's is what it gives me:

Broker EffMonth average MYAVERAGE
1000008308 01/09/2003 0
1000012946 01/07/2004 3492 3492
1000012946 01/09/2004 3582 3582
Click to expand...
 
Back
Top