sort cross table by VBA

[benjamin.grimm]

hello guys,

i have a cross table.

In the columns i have the months and in the rows i have country.

the cross table sums up in the specific months, how many busses will be build in the next 10 months.

So there wont be anything longer than one year. So the year does not matter.

Here my SQL Code for the cross table:

TRANSFORM Count([CC_400].where) AS AnzahlvonVerkaufsorg
SELECT [CC_400].[country], Count([CC_400].Verkaufsorg) AS [Gesamtsumme von Verkaufsorg]
FROM CC_400
GROUP BY [CC_400].[country]
PIVOT Format([Buildingdate],"mmm") In ("Jan","Feb","Mrz","Apr","Mai","Jun","Jul","Aug","Sep","Okt","Nov","Dez");

Now i want to sort the cross table.

Right now its:

xxxxxx Jan, Feb, Mrz, Apr, Mai .......
AT
BE
CH
DE
FR
.......

I want that it starts with the following months:

so like this (right now its march)

xxxxxxx Apr , Mai, Jun, Jul, Aug, .......
AT
BE
CH
DE
FR
.....


For this reason i created the querry: qsort

SQL code:

SELECT Tabelle_months.ID, Tabelle_months.months, ([ID])-Month(Date())+IIf(Tabelle_months.ID-Month(Date())<=0,12,0) AS SortAuf
FROM Tabelle_months;

and with the follwing VBA i want to sort the querry then like i described before.

    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    
    Dim qdf As DAO.QueryDef
    Dim intPos As Integer
    Dim strSort As String
    
    Set rs = CurrentDb.OpenRecordset("QSort", dbReadOnly, dbForwardOnly)
    Do Until rs.EOF
        strSort = strSort & ",""" & rs(1) & """"
        rs.MoveNext
    Loop
    
    strSort = "(" & Mid(strSort, 2) & ")"
    Set qdf = CurrentDb.QueryDefs("CC_500_cross_table")
    intPos = InStrRev(qdf.SQL, "In")
    qdf.SQL = Left(qdf.SQL, intPos + 1) & strSort & ";"
    
    rs.Close
    qdf.Close
    
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing

but it doesn´t work.

How can i change it ?

greetz benjamin

 

[namliam]

Try changing:
PIVOT Format([Buildingdate],"mmm") In ("Jan","Feb","Mrz","Apr","Mai","Jun","Jul","Aug","Sep","Okt","Nov","Dez");
to
PIVOT Format([Buildingdate],"mmm") In ("Apr","Mai","Jun","Jul","Aug","Sep","Okt","Nov","Dez", "Jan","Feb","Mrz");

 

[benjamin.grimm]

thanks for the answer.

If i change it like you said, then i have to change it every months.

I dont want to change it every months.

Do you know what i mean?

 

[namliam]

I know what you mean, however the PIVOT line forces the ordering which is a plus or a minus depending on your POV.

You can
1) Change your columns to YYYY-MM format, that should auto-format in the right order
2) make some VBA to "dynamicaly" order your pivot line each month.

 

[benjamin.grimm]

i changed my SQL now to

PIVOT Format([date],"YYYY-mm");

Now it show me like this

xxxxx 2014/04 2014/05 2014/06
AT
BE
CH
DE
FR
....

My only problem is that if there is no building date in the specific months, the months get skipped.

xxxxx 2014/04 2014/06
AT
BE
CH
DE
FR
....

greetz benjamin

 

[namliam]

Obviously, by default the crosstab will show only values that are actually in the database.

Pivot is one way of forcing the values to be there, dummy data is another... possibly union queries to get the dummy data.

Actually just to "fix" my earlier comment it isnt the actual PIVOT that is causing this it is the IN part of the pivot line....

 

[benjamin.grimm]

and how can i do this?

Is my approach wrong?

greetz benjamin

 

[namliam]

How can you do what? I gave you a few options along the way to solve this...

Yes your current approach as per my earlier posts is wrong, simply because such is the nature of things you work with... It is doing what you are telling it too, you want something else you need to tell it to do something else.

Easiest way is to "dynimicaly" adjust the IN of the Pivot command dynamicaly but its not really dynamic now is it... A lot harder is working with dummy data to ensure all your data is filled but that should ultimately be completely dynamic assuming working with YYYY-MM is acceptable.

 

[benjamin.grimm]

so i have the solution.

First of all create a sort querry.

QSort --> SQL:

SELECT tblMonat.Monat_ID, tblMonat.txtMonat 
FROM tblMonat 
ORDER BY ([monat_id])-Month(Date())+IIf([tblMonat].[Monat_id] 
-Month(Date())<=0,12,0);

and then i can transfer it with the following code directly to excel

[B]Code:[/B]Dim rs As DAO.Recordset 
Dim db As DAO.Database 

Dim qdf As DAO.QueryDef 
Dim intPos As Integer 
Dim strSort As String 

Set qdf = CurrentDb.QueryDefs("2_geplannt_CC_500_Kreuztabelle") 
intPos = InStrRev(qdf.SQL, "In") 
qdf.SQL = Left(qdf.SQL, intPos + 1) & "(" & Monthlist(Format(Date, "mmm"),true) & ");" 

rs.Close 
qdf.Close 

Set rs = Nothing 
Set qdf = Nothing 
Set db = Nothing 

DoCmd.OutputTo acOutputQuery, "2_geplannt_CC_500_Kreuztabelle", acFormatXLSX, "Export.xlsx", True

greetz benjamin

 

[namliam]

Yes adjusting the months in the IN clause works fine

I probably wouldnt have done a Select statement...

For I = 1 to 12
    Debug.print format(Dateadd("M",i,date())
next I

Depending on your exact needs, change the value of I to 0 to 11 or -1 to 10 or whatever 12 months period.