URGENT HELP NEEDED WITH GETTING AVERAGES BY QUARTER

I need some urgent help with quarter field

I have 3 columns in my database

OrderNo, OrderData, OrderAmount

say that the date right now is Jan 07
then I would like to see my averages for last 4 quarters so I will have
4 columns for my query

Current Quarter, Current Quarter -1 , Current Quarter - 2, Current
Quarter -3

so for Jan current quarter is 1st quarter of the year and any dates
from Oct - Dec 06 are in
Current Quarter - 1

and for Apr 07, current quarter is same and any dates from Jan - Mar 07
are current quarter -1
and any dates from Oct - Dec 06 are in Current Quarter - 2 and any
dates from


so whatever date I am in I would like this query to run for last 4
quarter.


I have this

select
avg(iif( datepart('q', date()) = datepart('q', orderdate) and
datepart('yyyy', date()) = datepart('yyyy', orderdate), orderamount,
0)) as [Average OrderAmount for Current Quarter]

avg(iif( datepart('q', date())-1 = datepart('q', orderdate) and
datepart('yyyy', date()) = datepart('yyyy', orderdate), orderamount,
0)) as [Average OrderAmount for Current Quarter -1]

avg(iif( datepart('q', date())-2 = datepart('q', orderdate) and
datepart('yyyy', date()) = datepart('yyyy', orderdate), orderamount,
0)) as [Average OrderAmount for Current Quarter -2]

avg(iif( datepart('q', date())-3 = datepart('q', orderdate) and
datepart('yyyy', date()) = datepart('yyyy', orderdate), orderamount,
0)) as [Average OrderAmount for Current Quarter -3]




but the second part is not true when the date is Jan through Sep for
the year.
The year has to be changed to current year -1 when such case exist. Any
solution for this.

Assuming OrderData is a typo and should be the date OrderDate...
Use a crosstab query with a column heading expression like:
ColHead: "Q" & DateDiff("q",[OrderDate], Date())
Set the Column Headings property to:
Column Headings: "Q0","Q1","Q2","Q3"

Q0 will display this quarter while Q3 will be 3 quarters ago.

I don't know what you mean by "my averages". Start by setting the crosstab
Value to Avg([OrderAmount])
--
Duane Hookom
Microsoft Access MVP


"Ashley" wrote:

I need some urgent help with quarter field

I have 3 columns in my database

OrderNo, OrderData, OrderAmount

say that the date right now is Jan 07
then I would like to see my averages for last 4 quarters so I will have
4 columns for my query

Current Quarter, Current Quarter -1 , Current Quarter - 2, Current
Quarter -3

so for Jan current quarter is 1st quarter of the year and any dates
from Oct - Dec 06 are in
Current Quarter - 1

and for Apr 07, current quarter is same and any dates from Jan - Mar 07
are current quarter -1
and any dates from Oct - Dec 06 are in Current Quarter - 2 and any
dates from


so whatever date I am in I would like this query to run for last 4
quarter.


I have this

select
avg(iif( datepart('q', date()) = datepart('q', orderdate) and
datepart('yyyy', date()) = datepart('yyyy', orderdate), orderamount,
0)) as [Average OrderAmount for Current Quarter]

avg(iif( datepart('q', date())-1 = datepart('q', orderdate) and
datepart('yyyy', date()) = datepart('yyyy', orderdate), orderamount,
0)) as [Average OrderAmount for Current Quarter -1]

avg(iif( datepart('q', date())-2 = datepart('q', orderdate) and
datepart('yyyy', date()) = datepart('yyyy', orderdate), orderamount,
0)) as [Average OrderAmount for Current Quarter -2]

avg(iif( datepart('q', date())-3 = datepart('q', orderdate) and
datepart('yyyy', date()) = datepart('yyyy', orderdate), orderamount,
0)) as [Average OrderAmount for Current Quarter -3]




but the second part is not true when the date is Jan through Sep for
the year.
The year has to be changed to current year -1 when such case exist. Any
solution for this.

Like Duane I do not know why average was used as it would average each sale
date.
Below will sum the quarter and display the quarters. It will automatically
use the current quarter and the last three quarters.

TRANSFORM Sum(Ashley.OrderAmount) AS SumOfOrderAmount
SELECT "" AS [Orders by Quarter]
FROM Ashley
WHERE (((Format([OrderDate],"yyyyq")) Between
Format(DateAdd("yyyy",-1,Date()),"yyyyq") And Format(Date(),"yyyyq")))
GROUP BY ""
ORDER BY "Qtr " & Format([OrderDate],"q") & " " & Year([OrderDate]) DESC
PIVOT "Qtr " & Format([OrderDate],"q") & " " & Year([OrderDate]);


"Ashley" wrote:

I need some urgent help with quarter field

I have 3 columns in my database

OrderNo, OrderData, OrderAmount

say that the date right now is Jan 07
then I would like to see my averages for last 4 quarters so I will have
4 columns for my query

Current Quarter, Current Quarter -1 , Current Quarter - 2, Current
Quarter -3

so for Jan current quarter is 1st quarter of the year and any dates
from Oct - Dec 06 are in
Current Quarter - 1

and for Apr 07, current quarter is same and any dates from Jan - Mar 07
are current quarter -1
and any dates from Oct - Dec 06 are in Current Quarter - 2 and any
dates from


so whatever date I am in I would like this query to run for last 4
quarter.


I have this

select
avg(iif( datepart('q', date()) = datepart('q', orderdate) and
datepart('yyyy', date()) = datepart('yyyy', orderdate), orderamount,
0)) as [Average OrderAmount for Current Quarter]

avg(iif( datepart('q', date())-1 = datepart('q', orderdate) and
datepart('yyyy', date()) = datepart('yyyy', orderdate), orderamount,
0)) as [Average OrderAmount for Current Quarter -1]

avg(iif( datepart('q', date())-2 = datepart('q', orderdate) and
datepart('yyyy', date()) = datepart('yyyy', orderdate), orderamount,
0)) as [Average OrderAmount for Current Quarter -2]

avg(iif( datepart('q', date())-3 = datepart('q', orderdate) and
datepart('yyyy', date()) = datepart('yyyy', orderdate), orderamount,
0)) as [Average OrderAmount for Current Quarter -3]




but the second part is not true when the date is Jan through Sep for
the year.
The year has to be changed to current year -1 when such case exist. Any
solution for this.