Monthly Sequence Sorting Order in Crosstab Query

[ahmad_rmh]

How to make the monthly sequence sorting order in crosstab query.

I have attached the screen shots of the query.

The criteria which have been used for the query is as under;

TRANSFORM Sum(Stock_In_Out) AS SumOfStock_In_Out
SELECT ItemsPK, ItemsListName
FROM qryTransactionsExtended
WHERE (((EntityPK)>4))
GROUP BY ItemsPK, ItemsListName
ORDER BY ItemsPK, Format([TranDate],"mmm - yyyy") DESC
PIVOT Format([TranDate],"mmm - yyyy");

 

[June7]

Either format the month and year as yyyymm or specify column headers. Review http://allenbrowne.com/ser-67.html#ColHead

 

[ahmad_rmh]

Either format the month and year as yyyymm or specify column headers. Review http://allenbrowne.com/ser-67.html#ColHead

Thanks, I have tried like as under

Month(Format([TranDate],"mmm-yyyy"))

But I want the months should have to show as like, mmm-yyyy whereas it's showing month numbers

 

[June7]

Format([TranDate], "yyyy-mm")

or

specify column headers as described in article.

Click the "dynamic monthly crosstab reports" link in that article for more information on solution to your situation.

 

[ahmad_rmh]

Format([TranDate], "yyyy-mm")

or

specify column headers as described in article.

Click the "dynamic monthly crosstab reports" link in that article for more information on solution to your situation.

Thanks It's working,

What's the logic used in programming "yyyy-mmm-dd"

 

[June7]

If dataset is multi-year and query retrieves data that crosses years, output will be in chronological order. However, cannot use mmm because that returns month short name and order is alphabetical.

 

[ahmad_rmh]

If dataset is multi-year and query retrieves data that crosses years, output will be in chronological order. However, cannot use mmm because that returns month short name and order is alphabetical.

But how could I get year name at the end?

 

[June7]

If you specify column names with PIVOT IN(), columns will be in order as you type them - as shown in Allen's article. Here is another recent discussion about CROSSTAB you might find interesting https://www.access-programmers.co.uk/forums/threads/union-crosstab-querie.326628/page-2#post-1865128

 

[ahmad_rmh]

If you specify column names with PIVOT IN(), columns will be in order as you type them - as shown in Allen's article. Here is another recent discussion about CROSSTAB you might find interesting https://www.access-programmers.co.uk/forums/threads/union-crosstab-querie.326628/page-2#post-1865128

I don't want to specify the column names

I want month name before year, what would be the technique, I should have to use.

 

[June7]

I don't know any other way to force this in query. You can build a report and put what you want in labels. Did you review the linked article in Allen Brown's tutorial?

You could write a bunch of VBA code and save data to a temp table.

 

[ebs17]

I want month name before year

If you want:

...
PIVOT Format([TranDate],"mmm - yyyy") IN ("Jan - 2022", "Feb - 2022", ...)

Use fixed columns. In your fixation you can make a "sorting" independently.

You just have to be aware that in a crosstab query column names are generated dynamically from existing data. Dynamic and fixation have a conflict potential against each other.

 

[ahmad_rmh]

If you want:

...
PIVOT Format([TranDate],"mmm - yyyy") IN ("Jan - 2022", "Feb - 2022", ...)

Use fixed columns. In your fixation you can make a "sorting" independently.

You just have to be aware that in a crosstab query column names are generated dynamically from existing data. Dynamic and fixation have a conflict potential against each other.

I don't want to fix column names as I have already mentioned in post #9

 

[ebs17]

No matter what you want: without fixed columns, they will be sorted alphabetically, and this will start with the first character, then the second, and so on.

So get your month names to line up alphabetically to match your desired order.

 

[ahmad_rmh]

My colmun names are changing dynamically

 

[ebs17]

As I said: You just have to be aware that in a crosstab query column names are generated dynamically from existing data.

You can create a sequence number on the month in the query, which would also be dynamic, and then use it.

PIVOT Format(SeqNumb, "00") & Format([TranDate]," - mmm - yyyy")

But this also detracts from the beauty of the view.

 

[ahmad_rmh]

As I said: You just have to be aware that in a crosstab query column names are generated dynamically from existing data.

You can create a sequence number on the month in the query, which would also be dynamic, and then use it.

PIVOT Format(SeqNumb, "00") & Format([TranDate]," - mmm - yyyy")

But this also detracts from the beauty of the view.

I have attached a sample db file, I have tried but not getting the results what i am expecting

 

[ebs17]

what i am expecting

You have to adjust your expectations to the given possibilities. As an example:

...
PIVOT
   Format([Date], "yyyy_mm - mmmm")

The alternative is a larger VBA project that must also accommodate and handle dynamics and transform query results into desired views.
You have to pay for such additional effort as well as probably compromise again elsewhere.

 

[ahmad_rmh]

You have to adjust your expectations to the given possibilities. As an example:

...
PIVOT
   Format([Date], "yyyy_mm - mmmm")

The alternative is a larger VBA project that must also accommodate and handle dynamics and transform query results into desired views.
You have to pay for such additional effort as well as probably compromise again elsewhere.

How the programmers are implementing months as like mmmm - yyyy

 

[ebs17]

Use a search on the internet with a search engine of your own choice and for example the following keywords:
crosstab query report

 

[Pat Hartman]

How the programmers are implementing months as like mmmm - yyyy

They are creating the SQL using VBA every time they run the query. That lets them keep changing the column headings programmatically. They are not hard coded.