How to sort monthly forecast accordingly in crosstab query

[Kamayo Ako]

Have a good day to all.

I have the following query to order by month but still not sort accordingly

TRANSFORM Sum(ForecastHistory.ForecastQty) AS SumOfForecastQty
SELECT ForecastHistory.Description
FROM ForecastHistory
GROUP BY ForecastHistory.Description
ORDER BY Format([ForecastDate],"mmmm - yyyy")
PIVOT Format([ForecastDate],"mmmm - yyyy");

STILL THE RESULT IS BELOW

Thank you so much.

 

[ebs17]

You can use fixed column headings:

...
PIVOT Format([ForecastDate],"mmmm - yyyy") IN ("July -2023", "August - 2023", "September - 2023")

 

[Kamayo Ako]

You can use fixed column headings:

...
PIVOT Format([ForecastDate],"mmmm - yyyy") IN ("July -2023", "August - 2023", "September - 2023")

thank you so much sir.

 

[Pat Hartman]

The problem with this format is two fold- month comes before year so if the period crosses year, the years might not be in order and the month name is alpha and therefore, the sort sequence is alpha and has nothing to do with what we think of as proper order for a date.

In order to get the columns sorted correctly, you need to change the date format to yyyy-mm. Then to get the display you want, you need code in the form or report (you CANNOT fix this problem in a query since there are no events in which you can place code), you can reformat the caption so you convert yyyy-mm to mmmm - yyyy using VBA.

If you can't figure out the code, let us know.

 

[Kamayo Ako]

The problem with this format is two fold- month comes before year so if the period crosses year, the years might not be in order and the month name is alpha and therefore, the sort sequence is alpha and has nothing to do with what we think of as proper order for a date.

In order to get the columns sorted correctly, you need to change the date format to yyyy-mm. Then to get the display you want, you need code in the form or report (you CANNOT fix this problem in a query since there are no events in which you can place code), you can reformat the caption so you convert yyyy-mm to mmmm - yyyy using VBA.

If you can't figure out the code, let us know.

Thank you so much sir. i will do it.

 

[Kamayo Ako]

The problem with this format is two fold- month comes before year so if the period crosses year, the years might not be in order and the month name is alpha and therefore, the sort sequence is alpha and has nothing to do with what we think of as proper order for a date.

In order to get the columns sorted correctly, you need to change the date format to yyyy-mm. Then to get the display you want, you need code in the form or report (you CANNOT fix this problem in a query since there are no events in which you can place code), you can reformat the caption so you convert yyyy-mm to mmmm - yyyy using VBA.

If you can't figure out the code, let us know.

I used below query and works fine sir.

TRANSFORM Sum(ForecastHistory.ForecastQty) AS SumOfForecastQty
SELECT ForecastHistory.Description
FROM ForecastHistory
WHERE (((Year([ForecastDate]))=Year(Date())))
GROUP BY ForecastHistory.Description
PIVOT Format([ForecastDate],"mmmm") In ("January","February","March","April","May","June","July","August","September","October","November","December");

You can use fixed column headings:

...
PIVOT Format([ForecastDate],"mmmm - yyyy") IN ("July -2023", "August - 2023", "September - 2023")

I use below query its working fine.. thank you..;
TRANSFORM Sum(ForecastHistory.ForecastQty) AS SumOfForecastQty
SELECT ForecastHistory.Description
FROM ForecastHistory
WHERE (((Year([ForecastDate]))=Year(Date())))
GROUP BY ForecastHistory.Description
PIVOT Format([ForecastDate],"mmmm") In ("January","February","March","April","May","June","July","August","September","October","November","December");

 

[Pat Hartman]

Good choice. That is an easy solution as long as your dates aren't a rolling period where you need to include year in the column header.

 

[Kamayo Ako]

Good choice. That is an easy solution as long as your dates aren't a rolling period where you need to include year in the column header.

Thank you sir.

 

[Kamayo Ako]

I understand your concerns about the date format and sorting order. In order to address these issues, you can follow the steps outlined below:

1. Change the date format to "yyyy-mm" in your data. This format ensures that the years and months are properly ordered when sorted alphabetically.
2. In your report or form, use VBA (Visual Basic for Applications) code to reformat the caption or display of the date. You can convert the "yyyy-mm" format to "mmmm - yyyy" format using VBA functions.

Here's an example of how you can achieve this in VBA:

vbaCopy code
' Assuming you have a TextBox named "txtDate" on your report or form

Private Sub Report_Open(Cancel As Integer)
' Get the current date in yyyy-mm format
Dim currentDate As String
currentDate = Format(Date, "yyyy-mm")

' Convert the date to mmmm - yyyy format
Dim formattedDate As String
formattedDate = Format(DateValue(currentDate), "mmmm - yyyy")

' Set the formatted date as the caption of the TextBox
Me.txtDate.Caption = formattedDate
End Sub

This code runs when the report or form is opened. It retrieves the current date in the "yyyy-mm" format, converts it to the "mmmm - yyyy" format using the Format function, and sets the formatted date as the caption of the TextBox.

By following these steps, you can ensure that your dates are correctly sorted and displayed in the desired format.

Yes, I will do that. Thank you so much