Crosstab query 1

[ozzroo]

Hi

I have the following cross tab query

<--
TRANSFORM Sum(test.CountOfAttended) AS SumOfCountOfAttended
SELECT test.Course, Sum(test.CountOfAttended) AS [Total Of CountOfAttended]
FROM test
GROUP BY test.Course
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
-->


Basically down the left is a list of courses, along the top is each of the months and the values are the sum of the people who attended each course per month.

How can I filter out one particular year.

Our year starts in April through to March. So what I want to display is April - December for one year, then January to March for the following year.

Any help would be appreciated

Thanks

AP

 

[PHV]

You may try this:
PARAMETERS [Enter start year] INTEGER;
TRANSFORM Sum(test.CountOfAttended) AS SumOfCountOfAttended
SELECT test.Course, Sum(test.CountOfAttended) AS [Total Of CountOfAttended]
FROM test
WHERE Format([Date],"yyyymm") Between [Enter start year] & "04" And (1+[Enter start year]) & "03"
GROUP BY test.Course
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ozzroo]

Thanks for your reply

The solution you gave me works about 90%. The problem is
Training began January 05. However if i type in 2004 in the parameter prompt I am getting data returned for Jan thru to May which is accurate data for 2005.

Where you have:
Between [Enter start year] & "04" And (1+[Enter start year]) & "03"

What does the 04 and 03 mean. do I have to change that each time i want to run the report for certain years.

I want to be able to select or type in a start year which takes the months april - december and then the next year either automatically or type in which takes january - march of that year.

so if i type in 2004 it would give me the stats for april - december 2004 + jan - mar 2005.

it only prompts be for the start year by the way

my sql at the moment which does bring back results like i mentioned however not according to year is:

PARAMETERS [Enter start year] Long;
TRANSFORM Sum(qry_TrainingTotals.CountOfModule) AS SumOfCountOfModule
SELECT qry_TrainingTotals.Module, Sum(qry_TrainingTotals.CountOfModule) AS [Total Of CountOfModule]
FROM qry_TrainingTotals
WHERE (((Format([Date],"yyyymm")) Between [Enter start year] & "04" And (1+[Enter start year]) & "05"))
GROUP BY qry_TrainingTotals.Module
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


thanks again. you solution worked. just needs tweaking i think

 

[PHV]

Replace this:
And (1+[Enter start year]) & "05"))
By this:
And (1+[Enter start year]) & "03"))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ozzroo]

Did that.

I know have:

PARAMETERS [Enter start year] Long;
TRANSFORM Sum(qry_TrainingTotals.CountOfModule) AS SumOfCountOfModule
SELECT qry_TrainingTotals.Module, Sum(qry_TrainingTotals.CountOfModule) AS [Total Of CountOfModule]
FROM qry_TrainingTotals
WHERE (((Format([Date],"yyyymm")) Between [Enter start year] & "03" And (1+[Enter start year]) & "03"))
GROUP BY qry_TrainingTotals.Module
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

See if I type in 2004 using that code. I can data in Jan & Feb. That data should be in 2005.

If I type in 2005 I get data in April, May, June which is correct but then its missing the Jan & Feb data

 

[PHV]

In my original post:
WHERE Format([Date],"yyyymm") Between [Enter start year] & [highlight]"04"[/highlight] And (1+[Enter start year]) & [highlight]"03"[/highlight]

So if you type 2004, the filter becomes:
WHERE Format([Date],"yyyymm") Between "200404" And "200503"
which means: Apr-Dec in 2004, Jan-Mar 2005.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ozzroo]

Works perfect!!!

Thanks mate.

Solution was appreciated!

 

[ozzroo]

Just another question. How can I get the headers along the top to display the month and year in the format of

Jan05 Feb05 Mar05 Apr06 May06

At the moment it just shows the months like Jan, Feb, Mar and so on

Do I need to change anything in this line below?

WHERE Format([Date],"yyyymm") Between [Enter start year] & "04" And (1+[Enter start year]) & "03"