Crosstab Query - Column sorting problem - Ascending

[Irshad Alam]

I am trying to make a crosstab query, below is sql view, all the rows and
values, columns are ok, no error. Only the problem is that its not sorting by
the column heading, April month is coming first, then febrauary,January march
One thing I can notice it is sorting based on the first field SIECode, But I
want to and have selected in design view to sort the column field in asending:

TRANSFORM Count(SlQ1.SlDays) AS CountOfSlDays
SELECT SlQ1.SlEcode, SlQ1.SlEName
FROM SlQ1
GROUP BY SlQ1.SlEcode, SlQ1.SlEName
ORDER BY Format([SlDates],"mmmm")
PIVOT Format([SlDates],"mmmm");

Please correct my code and some tips for handling complete year

Thanks and bes regards

Irshad

 

[KARL DEWEY]

Edit pivot line like this --
PIVOT Format([SlDates],"mmmm") IN("January", "February", "March", "April",
.... ,"December");

Fill in all months.

 

[Irshad Alam]

Dear Sir,

Thank you very much, I implemented and its worked perfect.

I would like to ask you additional question, that how to enter the
parameters. As it has few years data. Therefore, I tried the below:

1. Made two text box and made the format to dd-mm-yyyy, one to enter start
date and another to enter end date.

2. Opened the crosstab query, From Menu selected - Query - Parameters and
typed below :
Between [Forms]![SlDlgA]![Text16] And [Forms]![SlDlgA]![Text18]
Selected data type as date/time

3. In the below criteria row typed under the data field of column :
Between [Forms]![SlDlgA]![Text16] And [Forms]![SlDlgA]![Text18]

It did not worked. Need advise

My sql view as below :

PARAMETERS [Between [Forms]]![OsFormA]![Text31] And
[Forms]![OsFormA]![Text33] DateTime;
TRANSFORM Count(SlQ1.SlDays) AS CountOfSlDays
SELECT SlQ1.SlEcode, SlQ1.SlEName, Count(SlQ1.SlDays) AS TotalDays
FROM SlQ1
WHERE (((Format([SlDates],"mmmm")) Between [Forms]![OsFormA]![Text31] And
[Forms]![OsFormA]![Text33]))
GROUP BY SlQ1.SlEcode, SlQ1.SlEName
ORDER BY Format([SlDates],"mmmm")
PIVOT Format([SlDates],"mmmm") In ("January", "February", "March", "April",
"May", "June", "July", "August", "September","October","November",
"December");


Please advice.

Regards

Irshad

Edit pivot line like this --
PIVOT Format([SlDates],"mmmm") IN("January", "February", "March", "April",
... ,"December");

Fill in all months.

--
Build a little, test a little.

I am trying to make a crosstab query, below is sql view, all the rows and
values, columns are ok, no error. Only the problem is that its not sorting by
the column heading, April month is coming first, then febrauary,January march
One thing I can notice it is sorting based on the first field SIECode, But I
want to and have selected in design view to sort the column field in asending:

TRANSFORM Count(SlQ1.SlDays) AS CountOfSlDays
SELECT SlQ1.SlEcode, SlQ1.SlEName
FROM SlQ1
GROUP BY SlQ1.SlEcode, SlQ1.SlEName
ORDER BY Format([SlDates],"mmmm")
PIVOT Format([SlDates],"mmmm");

Please correct my code and some tips for handling complete year

Thanks and bes regards

Irshad

 

[KARL DEWEY]

Open the crosstab query, From Menu select - Query - Parameters and
type below on separate lines:
[Forms]![SlDlgA]![Text16]
[Forms]![SlDlgA]![Text18]
selecting DateTime for each.

PARAMETERS [Forms]![OsFormA]![Text31] DateTime, [Forms]![OsFormA]![Text33]
DateTime;
TRANSFORM Count(SlQ1.SlDays) AS CountOfSlDays
SELECT SlQ1.SlEcode, SlQ1.SlEName, Count(SlQ1.SlDays) AS TotalDays
FROM SlQ1
WHERE ([SlDates] Between [Forms]![OsFormA]![Text31] And
[Forms]![OsFormA]![Text33])
GROUP BY SlQ1.SlEcode, SlQ1.SlEName
PIVOT Format([SlDates],"mmmm") In ("January", "February", "March", "April",
"May", "June", "July", "August", "September","October","November",
"December");

--
Build a little, test a little.

Dear Sir,

Thank you very much, I implemented and its worked perfect.

I would like to ask you additional question, that how to enter the
parameters. As it has few years data. Therefore, I tried the below:

1. Made two text box and made the format to dd-mm-yyyy, one to enter start
date and another to enter end date.

2. Opened the crosstab query, From Menu selected - Query - Parameters and
typed below :
Between [Forms]![SlDlgA]![Text16] And [Forms]![SlDlgA]![Text18]
Selected data type as date/time

3. In the below criteria row typed under the data field of column :
Between [Forms]![SlDlgA]![Text16] And [Forms]![SlDlgA]![Text18]

It did not worked. Need advise

My sql view as below :

PARAMETERS [Between [Forms]]![OsFormA]![Text31] And
[Forms]![OsFormA]![Text33] DateTime;
TRANSFORM Count(SlQ1.SlDays) AS CountOfSlDays
SELECT SlQ1.SlEcode, SlQ1.SlEName, Count(SlQ1.SlDays) AS TotalDays
FROM SlQ1
WHERE (((Format([SlDates],"mmmm")) Between [Forms]![OsFormA]![Text31] And
[Forms]![OsFormA]![Text33]))
GROUP BY SlQ1.SlEcode, SlQ1.SlEName
ORDER BY Format([SlDates],"mmmm")
PIVOT Format([SlDates],"mmmm") In ("January", "February", "March", "April",
"May", "June", "July", "August", "September","October","November",
"December");


Please advice.

Regards

Irshad

Edit pivot line like this --
PIVOT Format([SlDates],"mmmm") IN("January", "February", "March", "April",
... ,"December");

Fill in all months.

--
Build a little, test a little.

I am trying to make a crosstab query, below is sql view, all the rows and
values, columns are ok, no error. Only the problem is that its not sorting by
the column heading, April month is coming first, then febrauary,January march
One thing I can notice it is sorting based on the first field SIECode, But I
want to and have selected in design view to sort the column field in asending:

TRANSFORM Count(SlQ1.SlDays) AS CountOfSlDays
SELECT SlQ1.SlEcode, SlQ1.SlEName
FROM SlQ1
GROUP BY SlQ1.SlEcode, SlQ1.SlEName
ORDER BY Format([SlDates],"mmmm")
PIVOT Format([SlDates],"mmmm");

Please correct my code and some tips for handling complete year

Thanks and bes regards

Irshad