Results 1 to 11 of 11
  1. #1
    jwreding is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    34

    Sorting Crosstab Query Column Headers with DatePart Function

    My goal: A crosstab query with weeks as column headers, showing a rolling 16 week report. Therefore, the most recent week would be column 1, previous week column 2, etc. back to 16 weeks ago.



    I am familiar with the method of forcing crosstab column headers to sort properly. Since I need the columns to change each week, showing the most recent week first and pushing each subsequent week out to the right, setting static column headers was a challenge. I was able to accomplish this by subtracting the current week number from the week number of the dates of the records, meaning I would have a result of -1, -2, etc. Then I could statically set the column headings as those numbers.

    Well this all broke down when the year switched over. Obviously subtracting Week 1 from Week 48 and such does not yield the correct result. To add even further complexity, the "week 53" issue is not helping matters either. I've tried several ways of working around this, but each one yields either an incorrect result, or a solution that I will have to modify near the end of the year.

    The best solution I have come up with so far abandons the static column headers. I have set my column headers to be Format([PickupDate],"yyyy-ww") and sorting in Descending order. It ALMOST works, but no matter what I do, I get two separate columns for the first and/or last week of the year. I have played with the [firstdayofweek] and [firstdayofyear] settings, but no matter what I always get either a "2014-1" and "2013-1" combination, or a "2013-52" and "2013-53" combo. Access seems to show Dec 29-31,2013 as either "Week 1" of 2013 (which makes no logical sense but I can see why it's happening), or "Week 53" of 2013 no matter what settings I use. If I could only figure out a way to combine those two partial weeks into one, I would have an acceptable solution (although I'd rather find a way to use static column headings in a perfect world).

    TL;DR - How can I get Access to recognize the week of Dec 29-Jan 4 as ONE contiguous week? Again, I have tried all of the settings in the FormatDate function to tweak the starting day of the week, etc. and I always get an additional "Week 53 of 2013" or "Week 1 of 2013" no matter what.

    Thanks in advance and I will be online all day to provide further explanation or post a file if necessary.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    can you provide a sample of your database with a working crosstab as you have it now.

    Just make a copy of your database, take out any information that's not relevant to this problem, make sure you have enough data to replicate the problem, then compact/repair, zip and upload the file to this website.

  3. #3
    jwreding is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    34
    File attached. As you see, I have tweaked the settings for the Format() function. Currently it is giving me a week 53 of 2013. Other settings give me a week 1 of 2013, even though it's really the last partial week of 2013 and first partial week of 2014.

    Thank you rpeare.
    Attached Files Attached Files

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Hrm... with this query you are not choosing a static set of weeks. For instance if you ran this today, then ran it again tomorrow you'd get different results even if none of the data had changed because some data would have moved weeks, is that what you intended? Or are you taking greater pains to suppress 'wrong' date selections in your actual database?

    You have two possibilities with this query

    1. The year ends ends on a saturday which coincides exactly with your week function so you have no problems (ends at 52 weeks)
    2. the year doesn't end on a saturday and you have a problem (ends at 53 weeks)

    What your query is actually doing is throwing december 29, 30 and 31 into week 53 of the 'old year' and january 1, 2, 3 and 4 into week 1 of the new year.

    so try this:

    Code:
    TRANSFORM Count(Loads.PRO) AS CountOfPRO
    SELECT Loads.CustomerID
    FROM Loads
    WHERE (((Loads.PickupDate) Between Date()-112 And Date()))
    GROUP BY Loads.CustomerID
    ORDER BY IIf(DatePart("ww",[pickupdate])=53,(Format(CDate("1/1/" & DatePart("yyyy",Date())),"yyyy-ww")),Format([pickupdate],"yyyy-ww")) DESC 
    PIVOT IIf(DatePart("ww",[pickupdate])=53,(Format(CDate("1/1/" & DatePart("yyyy",Date())),"yyyy-ww")),Format([pickupdate],"yyyy-ww"));
    I'm basically saying that if the week is 53, convert the date of the first day of the year's week rather than using the 53 value.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    sorry I didn't say that first part very well, if you ran this today, then again tomorrow, some of the data on the 'first' week would have dropped off and some of the data on the last week would have changed as well.

  6. #6
    jwreding is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    34
    In most of my queries, the WHERE clause is actually

    "WHERE ((DatePart("ww", [Loads].[PickupDate] Between DatePart("ww", Date()-112 And DatePart("ww", Date()))"

    But I removed all of the DatePart stuff just for clarity while I was trying to solve this other problem. I don't believe it's related to the particular issue I'm talking about regarding the column headings. Note the above is not syntactically correct because I just typed it in here, but you get the idea.


    Since I'm using the "ww" part of the date, it considers any dates from Sun-Sat of a particular week to be in that week number. So (if I'm correct) the query results won't change until this Sunday, when the data that is currently 1 week old will now be 2 weeks old.

    You are correct that I don't want a static set of weeks. People want to look at the query and see live rolling data going back a certain number of weeks (16). Then that 16th week will "fall off" the query when the next week rolls around. Basically looking at trends comparing week to week.

    But all of the above babbling is unnecessary, because I tried your SQL and it seems to work perfectly! Even better, from reading it I understand what you're doing there so it will help me with future queries.

    Thanks very much.

  7. #7
    jwreding is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    34
    Follow up...

    Will converting any week 53 dates to the first week of the year create problems since Access sees it as week 53 of 2013? Will it convert these to week 1 of 2013 or 2014? I am thinking 2013 since you are using the year of the pickupdate, which would be 2013. Is it necessary to add 1 to the year when converting week 53?

    Looking at the results of your query, it doesn't show any values for week 1 of 2013, so it is apparently working correctly. I am just trying to understand why.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Any days that would fall into week 53 of any given year should be grouped into the first week of the following year based on the formula I gave you.

    That formula, if used in other queries should give the same result. You can test this by just building a query based on your form and putting in the formula I gave you

    like this:


    Code:
    SELECT Loads.PRO, Loads.CustomerID, Loads.PickupDate, IIf(DatePart("ww",[pickupdate])=53,(Format(CDate("1/1/" & DatePart("yyyy",Date())),"yyyy-ww")),Format([pickupdate],"yyyy-ww")) AS Expr1
    FROM Loads
    ORDER BY Loads.PickupDate DESC;

  9. #9
    jwreding is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    34
    Thanks...and yes it does appear to work. I was looking from more of an academic/learning standpoint now since the problem is solved (and I'll mark it as such as soon as I find where to do so).

    Breaking apart the expression in my mind, it is saying that IF the week of the PickupDate is 53 then it uses CDate to create a new date of 1/1/yyyy, using the year of the PickupDate. So for a date such as 12/29/2013, which is in week 53 of 2013, I would think it would see that as week 53, then build a new date of 1/1/2013, since the value of "yyyy" for this PickupDate is 2013, not 2014. I am missing where it is converting the year of the PickupDate in the record to the subsequent year.

    OH. I see what's happening now. I was misreading the CDate expression. It converts to the current year, not the year of the PickupDate! I understand now. I was about to delete this reply, but thought I would post it anyway in case anyone else has the same question.

    Thanks again rpeare.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    no problemo, I frequently break down my formulas into smaller parts before putting them together, helps make sure I"m getting the right result as I go along.

  11. #11
    jwreding is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    34
    A follow up note to anyone interested, or who may search this topic at a later date:

    I was finally able to achieve the static column headings using rpeare's tip on converting week 53 to 52 and other uses of the Format() function. The ridiculous if statement below will give you a rolling 16 week (or any number of weeks depending on the date criteria you use) report with static column headings that show "-1" for last week, "-2" for 2 weeks ago, etc. This is nice because it allows you to actually make a form based on your query to add formatting and other expressions in text boxes (like % growth and such).

    The if statement:

    IIf(IIf(DatePart("ww",[PickupDate])=53,(Format(CDate("1/1/" & DatePart("yyyy",Date())),"ww")),Format([PickupDate],"ww")) - DatePart("ww", Date()) >=0, IIf(DatePart("ww",[PickupDate])=53,(Format(CDate("1/1/" & DatePart("yyyy",Date())),"ww")),Format([PickupDate],"ww")) - (DatePart("ww", Date()) + 52), IIf(DatePart("ww",[PickupDate])=53,(Format(CDate("1/1/" & DatePart("yyyy",Date())),"ww")),Format([PickupDate],"ww")) - (DatePart("ww", Date()))

    In English, it subtracts the current week number from the week of the date on the record (in this case PickupDate). If the result is positive (as would happen when you move across a changing year, e.g. week 51 - week 2), it simply adds 52 to the current week number. If the result is negative, it does the original operation. It's really not a complex expression, it's just a very long sub-expression repeated many times.

    Thanks again and I hope someone else is helped by this in the future. I know for my company specifically, it is a great metric to track attrition and other sales/profit trends in a manner such as this.

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

Similar Threads

  1. Crosstab Column Sorting
    By Paul H in forum Queries
    Replies: 2
    Last Post: 09-12-2013, 03:21 PM
  2. Sorting Crosstab query results?
    By Dragongem in forum Queries
    Replies: 1
    Last Post: 06-13-2013, 09:21 PM
  3. Replies: 3
    Last Post: 02-21-2012, 10:15 AM
  4. Replies: 2
    Last Post: 08-17-2011, 03:02 AM
  5. Dynamic Column Headers but NOT CROSSTAB
    By jtkjames in forum Queries
    Replies: 1
    Last Post: 07-26-2010, 05:16 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