Results 1 to 2 of 2

Crosstab Query with Week Columns Sort by Year?

  1. #1
    trevelyan is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    1

    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 online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    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.

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

Similar Threads

  1. Replies: 2
    Last Post: 12-24-2018, 04:12 PM
  2. 2 week intervals in Crosstab qry columns
    By mlrucci in forum Queries
    Replies: 4
    Last Post: 10-22-2018, 05:20 PM
  3. Replies: 2
    Last Post: 04-14-2016, 05:30 PM
  4. Replies: 5
    Last Post: 08-14-2015, 01:53 PM
  5. Replies: 3
    Last Post: 06-22-2015, 05: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
  •  
Tech Forums: Microsoft Office Forums