Results 1 to 6 of 6
  1. #1
    trevelyan is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    3

    Crosstab Query with Week Columns Sort by Year?

    I have created a cross tab query that has Material Numbers for the rows, with Week Numbers across the top. The problem I am having is the data contains 52 weeks worth of information. The information starts in the current week and goes out into next year. Id like my cross tab to start with the current week of this year and roll forward to the end of this year and then continue with Week 1 of next year. But the columns headings are showing Week 1 of next year and counting up to the current week of this year. The data is all out of order. Here is the SQL. Id like to Sort the PIVOT DatePart by year but not show the year in the crosstab.

    TRANSFORM Sum(PLAF.QTY) AS SumOfQTY
    SELECT PLAF.Material


    FROM PLAF
    WHERE (((PLAF.[Firm Status]) Is Null) AND ((PLAF.Material) Is Not Null))
    GROUP BY PLAF.Material
    PIVOT DatePart("ww",PLAF.[Finish Date]);

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want a 'rolling' weeks query so the current week will always be week1. Review http://www.access.hookom.net/Dynamic...rosstabRpt.htm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    trevelyan is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    3
    I looked at that and im not using a form. It doesnt make any sense. I am pulling data directly from a table using the above SQL but it wont sort properly. How can I sort it correctly? At this point id even take something like DatePart("YYWW",PLAF.[Finish Date]) and then LEN the Year off in Excel but YYWW wont work in query either. How can i get the column sorted properly? The other problem I have with my data is that WK0 may not be this week, it could be Next week or the following week. So I would always want to see the week number of the most recent set of data as the first week and it go out 52 weeks from there. I need to be able to see the week number. So say we are in week 28 right now. My data might actually not start until Week 30. I may not have week 28 or 29 data. I dont want Week 30 renamed to WK0 as i dont know if that WK0 is Week 28, 29 or 30.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what about

    format([Finish Date],"yy") & format(datepart("ww",[Finish Date]),"00")

  5. #5
    trevelyan is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    3
    Figured out a round about way of doing it and then just using a Right() Formula in Excel to drop off the year. For anyone else that would ever need to do this here is the update to the SQL.

    TRANSFORM Sum(PLAF.QTY) AS SumOfQTY
    SELECT PLAF.Material
    FROM PLAF
    WHERE (((PLAF.[Firm Status]) Is Null) AND ((PLAF.Material) Is Not Null))
    GROUP BY PLAF.Material
    PIVOT Year([PLAF].[Finish Date]) & DatePart("ww",[PLAF].[Finish Date]);

    Id love to have the cross tab just do it for me so it shows the week num instead of the year and week num. But I have been unsuccessful in having the cross tab sort properly.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    But I have been unsuccessful in having the cross tab sort properly.
    define sort properly - you seem to want

    48, 49, 50, 51, 52, 1, 2, 3

    to mean sorted properly

    you can specify column headings in the order you want - but then you want it to change every week as you move through the weeks of the year

    the only way you could do that with any degree of automation would be to use VBA to add the column headings to the pivot

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 12-24-2018, 05:12 PM
  2. 2 week intervals in Crosstab qry columns
    By mlrucci in forum Queries
    Replies: 4
    Last Post: 10-22-2018, 06:20 PM
  3. Replies: 2
    Last Post: 04-14-2016, 06:30 PM
  4. Replies: 5
    Last Post: 08-14-2015, 02:53 PM
  5. Replies: 3
    Last Post: 06-22-2015, 06:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums