Change Start of Year Month in Query

[G Marshall]

Hi
I have a query which shows fuel consumption per quarter by date selection.
When I run the query, consumptions for Jan, Feb & Mar are shown as quarter 1. How can I change my Query to Show April, May & June as quarter 1. My report is based on the financial year which runs from April to March.
Appreciate any Help

Gerald

 

[anvil19]

Hi gerald

Could you post the SQL statement you are using to get your data.

anvil19

 

[G Marshall]

Hi
This is the SQL used in my Query, Incidentally in my previous mail I said that I wanted "Fuel Consumptions per Quarter" It should have read "Quantity Received per quarter "

Cheers

Gerald

TRANSFORM Sum([Order History].[Quantity Received]) AS ProductAmount
SELECT [Order History].[Site Name], [Order History].[Order Complete], Year([Date Received]) AS OrderYear
FROM [Order History]
GROUP BY [Order History].[Site Name], [Order History].[Order Complete], Year([Date Received])
PIVOT "Qtr" & DatePart("q",[Date Received],1,0);

 

[anvil19]

Hey Gerald

Until today I had never heard of the DatePart Function. It's fantastic. There are a whole bunch of queries on these boards from people that want to be able to query a database and get data sorted by workdays, weekends etc. Brillient.

Now, to your question. According to the Microsoft help (Knowledge Base and Access Help) the datepart function uses "q" as a quarter, but of course it will use American quarters. These, I imagine, are reporting quarters that US business uses to announce as well as predict corporate results.

To change your quarter to reflect April to June you could just alter the statement to search from a date to a date, like so

TRANSFORM Sum([Order History].[Quantity Received]) AS ProductAmount 
SELECT [Order History].[Site Name], [Order History].[Order Complete],  
FROM [Order History]
WHERE [Date Recieved] >= #01/04/2004# AND <= #30/07/2004#
GROUP BY [Order History].[Site Name], [Order History].[Order Complete], Year([Date Received]) 
PIVOT Year([Date Received]) AS OrderYear;

Try That

anvil19

 

[G Marshall]

Hi There

Thanks for your interest in my question and your suggestion is a good one, however you may have slightly misunderstood exactly what I wanted. I wish to run my query and have an output with headings something like the following.

Location Qtr1 Qtr2 Qtr3 Qtr4

MySite 1500 2500 500 3000

I want Qtr1 to run from April to June, Qtr2 to run from July to August and so on. The SQL in my previous correspondence does this with one exception and that is that it will only recognise Jan to Mar as Qtr1 April to June as Qtr2 and so on. This is probably as clear as mud, but I do appreciate your help anyway.


Cheers

Gerald

 

[Cbrine]

Have you thought of Using DatePart to extract the Month Value from the date, then linking the query to a seperate table that reflects the pivot values you want to use?

Ie. Setup a table like this.

4.....Qtr1
5.....Qtr1
6.....Qtr1
7.....Qtr2
8.....Qtr2
etc...

I use the MSaccess query builder to most queries, so I can't tell you how to setup your query to link in this data, but it looks like you have a better handle on th SQL then I do. Hope this helps.

 

[mdmilner]

Would this work?

TRANSFORM Sum([Order History].[Quantity Received]) AS ProductAmount 
SELECT [Order History].[Site Name], [Order History].[Order Complete], Year([Date Received]) AS OrderYear 
FROM [Order History] 
GROUP BY [Order History].[Site Name], [Order History].[Order Complete], Year([Date Received]) 
PIVOT "Qtr" & IIF(DatePart("q",[Date Received],1,0)=0,4,DatePart("q",[Date Received],1,0)-1);

I didn't try this, but wouldn't be surprised if Access chokes on it.
<edit>
Well, just tried it. Worked just fine for me. It does not choke on the complex PIVOT statement.

Mike

 

[G Marshall]

Mike & Co,
Sorry for the delay in replying, I was off the air for a few days. Thanks for all your help, I used your example and it has worked just fine, the only change I had to make was the bit highlighted in red. I know very little about SQL but when I ran the query with the original SQL the quarter headings were Qtr0 and Qtr1. I had only information entered for two quarters. With a bit of trial and error I changed the little bit of code highlighted and hey presto! it worked perfectly. Thanks again for all your help, it was much appreciated.


PIVOT "Qtr" & IIF(DatePart("q",[Date Received],1,0)=0,4,DatePart("q",[Date Received],1,0)-1);

PIVOT "Qtr" & IIF(DatePart("q",[Date Received],1,0)=1,4,DatePart("q",[Date Received],1,0)-1);


Cheers


Gerald