Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    139

    Unanswered: Crosstab w/ columns in date order

    I have been trying to put together a crosstab query that would have the columns in date order. However, I have been unsuccessful. Here is the last code that I have tried:

    TRANSFORM Sum(crosstab.SumOfQTYSHP) AS SumOfSumOfQTYSHP
    SELECT crosstab.PRODID, Sum(crosstab.SumOfQTYSHP) AS [Total Of SumOfQTYSHP]
    FROM crosstab
    GROUP BY crosstab.PRODID
    PIVOT (Year([crosstab.shpdt])*12+Month([crosstab.shpdt])-1) & (Format([crosstab.shpdt],"mmm"" ' ""yy"));

    The column headers are appearing as 24082 Nov' 06 ... The months are in proper order now but it should not begin with the "24082". Could someone please look at this code to determine what changes I should make.

    I appreciate any help!
    B&R

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    if
    you are getting "24082 Nov' 06"

    from
    PIVOT (Year([crosstab.shpdt])*12+Month([crosstab.shpdt])-1) & (Format([crosstab.shpdt],"mmm"" ' ""yy"));

    this Year([crosstab.shpdt])*12+Month([crosstab.shpdt])-1) = 24082
    (Format([crosstab.shpdt],"mmm"" ' ""yy")) = Nov' 06

    the & join the data as one

    1&1 = 11
    1+1 = 2
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Feb 2004
    Posts
    139
    Thanks Myle! I did understand why it is doing what it is doing but I do not know how to make it do what I want it to do. I would like for the dates to be in order but only display YY MMM. The & symbol is putting both together but I have tried a comma and a space but neither will work. What do I need to do to keep the dates in order and only display the YY MMM?

    Again, appreciate your time and help!
    B&R

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Format([crosstab.shpdt],"yy MMM") will show what you want.

    My understand is the PIVOT has to be built before it can put data into it

    also if you are going to Print the data in a report then the coloum name will be changing each year.

    fix the first problem

    Dim SQL
    Dim DB as Database
    Dim RS as recordset
    Dim PIVOTtxt as String
    set DB = currentDB
    SQL = "Select shpdt FROM crosstab GroupBY crosstab.shpdt"
    RS = DB.open(SQL)
    Do until RS.eof
    PIVOTtxt = PIVOTtxt & format(rs.feild("shpdt"),"YY mmm") & " , "
    rs.movenext
    loop
    rs.close
    set rs = nothing


    take out Off last ,

    PIVOTtxt = mid(PIVOTtxt,1,len(PIVOTtxt)-2)

    now the PIVOTtxt should have "09 Feb , 09 Mar , 09 Apr"
    can't remeber if you need the [] in the line

    NewSQL = "TRANSFORM Sum(crosstab.SumOfQTYSHP) AS SumOfSumOfQTYSHP
    NewSQL = NewSQL & " SELECT crosstab.PRODID, Sum(crosstab.SumOfQTYSHP) AS [Total Of SumOfQTYSHP]"
    NewSQL = NewSQL & " FROM crosstab"
    NewSQL = NewSQL & "GROUP BY crosstab.PRODID"
    NewSQL = NewSQL & "PIVOT " & PIVOTtxt

    This is untested just off top of head ok.
    Last edited by myle; 03-16-09 at 19:14.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •