Date Query

[Bentslide]

I am trying to query the entries for a particular month in a table. My table consists of 4 colums, date, and 3 different interest rates. I want to be able to enter the month and the query generate rates for that month and average them. Any help would be great

 

[mpastore]

Create a query and paste this into it. You will have to change the names of the table and fields to match your actual table/field names:


select month([mydate]) as dt_month,avg([field1),avg([field2),avg([field3)
from [mytable]
where month([mydate]) = [Please enter a month]
group by month([mydate])


When you run the query it will prompt you for the month, enter 1 thru 12

That query assumes you have more than one record for each month and you want to average each field separately. If you want to average the fields in the actual record do:

select month([mydate]) as dt_month,([field1)+[field2]+[field3])/3
from [mytable]
where month([mydate]) = [Please enter a month]
[/b]



Mike Pastore

Hats off to (Roy) Harper

 

[Bentslide]

Thanks alot Mike but it is giving me a syntax error, "missing operator in query expression" this is what my SQL reads:

select month([date]) as dt_month,avg([3_month_discount_rate),avg([3_month_coupon_rate),avg([3_month_low_coupon_rate)
from [rates]
where month([date) = [Please enter a month]
group by month([date])

 

[lespaul]

if this just isn't a typo you are missing the other ]

select month([date]) as dt_month,avg([3_month_discount_rate),avg([3_month_coupon_rate),avg([3_month_low_coupon_rate)
from [rates]
where month([date]) = [Please enter a month]
group by month([date])



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Bentslide]

Yeah it was a typo but that didn't work either.... Im stumped thanks for your help

 

[Eightball3]

It looks like you have more than the one bracket missing. At the end of _rate I believe there should also be brackets.

 

[Bentslide]

Thanks but now Im getting a message that it is too complex to be evaluated, and the expression is typed incorrectly

Tom

 

[mpastore]

Post your final SQL exactly as it appears in the query.

Mike Pastore

Hats off to (Roy) Harper

 

[Bentslide]

SELECT Month([date]) AS dt_month, Avg(rates.[3_month_discount_rate]) AS AvgOf3_month_discount_rate, Avg(rates.[3_month_coupon_rate]) AS AvgOf3_month_coupon_rate, Avg(rates.[3_month_low_coupon_rate]) AS AvgOf3_month_low_coupon_rate
FROM rates
WHERE (((Month([date]))=[Please enter a month]))
GROUP BY Month([date]);

thanks your help is greatly appreciated....
Tom

 

[Bentslide]

I am ultimately getting a data type mismatch I really am haviong an issue with getting the averages for any kind of query

 

[Bentslide]

Thanks for all your help i figured it out... stupid problem on my part!!

TOm