Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2014
    Posts
    24

    Unanswered: 15 Months in Cross-tab or ?

    hello,
    I'm so close. I need help in finding out, if it's even possible, to get more than 12 months in a cross-tab?

    I know that you can IF you format a date field to show the year "mmm-yy", however I can't show the year.

    The year is dynamic whereas the months will remain static.

    Mar-Jun (15 months)

    I have this
    Code:
    TRANSFORM Count(dbo_v030mbrshp01PdMembers.MembershipNumber) AS CountOfMembershipNumber
    SELECT dbo_v030mbrshp01PdMembers.MemberType
    FROM dbo_v030mbrshp01PdMembers
    WHERE (((IIf(Month([PaymentDate])<=2,Year([PaymentDate])-1,Year([PaymentDate])))>=Year(Date())-1) AND ((dbo_v030mbrshp01PdMembers.MemberTypeID)=1 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=2 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=4) AND ((dbo_v030mbrshp01PdMembers.PaymentDate) Between #3/1/2013# And #6/30/2014#))
    GROUP BY dbo_v030mbrshp01PdMembers.MemberType
    ORDER BY Format([PaymentDate],"mmm-yy")
    PIVOT Format([PaymentDate],"mmm-yy");
    NOTE: the
    Code:
    ((dbo_v030mbrshp01PdMembers.PaymentDate) Between #3/1/2013# And #6/30/2014#))
    part was only for testing.
    It's actually
    Code:
    Between IIf(Month(Date())<=3,DateSerial(Year(Date())-1,3,1),DateSerial(Year(Date()),3,1)) And IIf(Month(Date())<=3,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30))
    but in order to see results I used the hard dates.

    If this doesn't work, the other thing I've tried is:
    this: How to create a running totals query in Microsoft Access

    No good. Getting #Error# or
    it freezes

    Any idea why?

    Code:
    SELECT dbo_v030mbrshp01PdMembers.MemberType, Year([PaymentDate]) AS PaidYr, Month([PaymentDate]) AS FM, IIf(Month([PaymentDate])<=5,Year([PaymentDate])-1,Year([PaymentDate])) AS FY, DCount("dbo_v030mbrshp01PdMembers","PaymentDate","DatePart('m', [PaymentDate])<=" & [FM] & " And  DatePart('yyyy',[PaymentDate])<=" & [FY] & "") AS RunTot
    FROM dbo_v030mbrshp01PdMembers
    WHERE (((dbo_v030mbrshp01PdMembers.PaymentDate) Between IIf(Month(Date())<=6,DateSerial(Year(Date())-1,6,1),DateSerial(Year(Date()),6,1)) And IIf(Month(Date())<=6,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30))))
    GROUP BY dbo_v030mbrshp01PdMembers.MemberType, Year([PaymentDate]), Month([PaymentDate]), IIf(Month([PaymentDate])<=5,Year([PaymentDate])-1,Year([PaymentDate]))
    HAVING (((IIf(Month([PaymentDate])<=5,Year([PaymentDate])-1,Year([PaymentDate])))>=Year(Date())-1))
    ORDER BY Year([PaymentDate]), Month([PaymentDate]);
    or even this:
    Code:
    SELECT dbo_v030mbrshp01PdMembers.MemberType, 
    
    IIf(Sum(IIf((DatePart("m",[PaymentDate])=3 Or DatePart("m",[PaymentDate])=4) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And DatePart("m",[PaymentDate])<=4,1,0))) AS Apr, 
    
    IIf(Sum(IIf(DatePart("m",[paymentdate])>=3 And 4 And DatePart("m",[paymentdate])<=5,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=3 And 4 And DatePart("m",[paymentdate])<=5,1,0))) AS May, 
    
    IIf(Sum(IIf(DatePart("m",[paymentdate])>=3 And 4 And DatePart("m",[paymentdate])<=6,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=3 And 4 And DatePart("m",[paymentdate])<=6,1,0))) AS Jun, 
    
    IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=7,1,0))) AS Jul, 
    
    IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=8,1,0))) AS Aug, 
    
    IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, 
    
    IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=10,1,0))) AS Oct, 
    
    IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 And DatePart("m",[PaymentDate])<=11,1,0))) AS Nov, 
    
    IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4,1,0))) AS [Dec], 
    
    IIf(Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=1,1,0))) AS Jan, 
    
    IIf(Sum(IIf(DatePart("m",[PaymentDate])=2,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=2,1,0))) AS Feb, 
    
    IIf(Sum(IIf(DatePart("m",[PaymentDate])=3 And DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=3,1,0))) AS Mar, 
    
    IIf(Sum(IIf(DatePart("m",[PaymentDate])=4 And DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=4,1,0))) AS Apr2
    
    IIf(Sum(IIf(DatePart("m",[PaymentDate])=5 And DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=5,1,0))) AS May2
    
    IIf(Sum(IIf(DatePart("m",[PaymentDate])=6 And DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=3 And 4 Or DatePart("m",[PaymentDate])<=6,1,0))) AS Jun2
    
    FROM dbo_v030mbrshp01PdMembers
    
    WHERE (((dbo_v030mbrshp01PdMembers.PaymentDate) 
    
    Between IIf(Month(Date())<=4,DateSerial(Year(Date())-1,4,1),DateSerial(Year(Date()),4,1)) And IIf(Month(Date())<=4,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30))) 
    
    AND ((dbo_v030mbrshp01PdMembers.MemberTypeID)=1 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=2 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=4))
    
    GROUP BY dbo_v030mbrshp01PdMembers.MemberType, dbo_v030mbrshp01PdMembers.MemberTypeID
    ORDER BY dbo_v030mbrshp01PdMembers.MemberType, dbo_v030mbrshp01PdMembers.MemberTypeID;
    but need help changing each column to to when the fiscal year changes:
    Code:
    Between IIf(Month(Date())<=4,DateSerial(Year(Date())-1,4,1),DateSerial(Year(Date()),4,1)) And IIf(Month(Date())<=4,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30)))
    as well as this one but limited to 13 months. I need 15 months
    Code:
    TRANSFORM Count(dbo_v030mbrshp01PdMembers.MembershipNumber) AS CountOfMembershipNumber
    SELECT dbo_v030mbrshp01PdMembers.MemberTypeID, dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType
    FROM dbo_v030mbrshp01PdMembers
    WHERE (((dbo_v030mbrshp01PdMembers.MemberTypeID)=1 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=2 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=4) AND ((dbo_v030mbrshp01PdMembers.PaymentDate) Between IIf(Month(Date())<=6,DateSerial(Year(Date())-1,6,1),DateSerial(Year(Date()),6,1)) And IIf(Month(Date())<=6,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30))) AND ((dbo_v030mbrshp01PdMembers.EndYear)>=Year(Date())))
    GROUP BY dbo_v030mbrshp01PdMembers.MemberTypeID, dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType
    PIVOT "Mth" & DateDiff("m",[PaymentDate],IIf(Month(Date())<=6,DateSerial(Year(Date())-1,6,30),DateSerial(Year(Date()),6,30))) In ("Mth0","Mth-1","Mth-2","Mth-3","Mth-4","Mth-5","Mth-6","Mth-7","Mth-8","Mth-9","Mth-10","Mth-11","Mth-12");

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    I do 15 month Xtabs all the time...
    get all your date stuff and other calcs done in the previous query.
    The Format(Date,"yyyy-mm"), etc...

    Then Xtab THAT query...this way the Xtab takes ONLY what it needs and doesnt have to perform math making it more complicated.

  3. #3
    Join Date
    Apr 2014
    Posts
    24
    Quote Originally Posted by ranman256 View Post
    I do 15 month Xtabs all the time...
    get all your date stuff and other calcs done in the previous query.
    The Format(Date,"yyyy-mm"), etc...

    Then Xtab THAT query...this way the Xtab takes ONLY what it needs and doesnt have to perform math making it more complicated.
    Well, yes, it's easy enough IF you had the year part but I noted that it can't have that since years will change.

    The months labels won't but the years change.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Dates are binary beasties, the display formatting is done at presentation time. I'm nearly certain that you can build the crosstab display using the dates, then apply formatting to only show the months so that you can confuse people.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Apr 2014
    Posts
    24
    Quote Originally Posted by Pat Phelan View Post
    Dates are binary beasties, the display formatting is done at presentation time. I'm nearly certain that you can build the crosstab display using the dates, then apply formatting to only show the months so that you can confuse people.

    -PatP
    ?

    Formatting is limited to 13 months from what I've found.

    Any idea how to do so for 15 or otherwise?
    Last edited by angelsupport; 05-16-14 at 13:33.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not expressing myself very well...

    Do the crosstab using the full dates so that you can do as many months as you want. Once you've done the crosstab, change the formatting of the displayed value to only show the information that you want (I think that is a month). This way you can crosstab however many months you want, there's no logical limit only a practical one.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Apr 2014
    Posts
    24
    Ok, I'm not following ...

    How do you mean "cross-tab with full dates"?

    I was immediately confused with the format using year. Hence my reply on how it can't be formatted with year since the years change whereas the months do not.

    It'll always be Mar-Jun

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What Pat means is that internally Access stores and handle a date in a bynary format. It's a Numeric Double where the integer part represents the number of days elapsed since a pivot day, while the decimal part represents the number of seconds elapsed since midnight. You can verify it by opeing the immediate window (Ctrl+G) and type in:
    Code:
    ? cdate(1)    ' --> 31/12/1899 
    ? cdbl(now)   ' --> 41775,8160532407 
    ? Clng(now)   ' --> 41776 (rounding)
    When you use a Date/Time type column in a query, the whole value is used, event if you elect to only display a part of it through the use of the Format() function of any other function that extract a part of the date/time value.

    Then nothing prevents you from using the whole column in the query and apply whatever format you want on it. The Year part will still be there, even if it does not appear.
    Have a nice day!

  9. #9
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    My crosstab has 20 months.

  10. #10
    Join Date
    Apr 2014
    Posts
    24
    Ok, but HOW?

    Great! Would you please share how you have 20 months WITHOUT the years?

    Thank you!

Tags for this Thread

Posting Permissions

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