Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Combine months and years in Query (XP)

    This may have been asked and answered in a more articulate manner, but I can't find it. Sorry.

    Have any of you come across a situation where your user wants to compare a length of time in a chart (or query) where the time span is not constant? In this case the user wants to compare the length of time a person was with the company before certain events occurred. I created a nice pareto chart providing what he asked for ( a graph comparing length of service at time of event), but not what he wanted.

    Instead of a graph showing 0, 1, 2, 3, 4 etc. years (which is what he originally asked for) he wants a graph of events at:
    : 0-3 months,
    : >3 <=6 months,
    : >6 <=9 months and
    : >9 <=12 months then
    : one year increments after the first 12 months

    Does that make sense? Is it possible to create a query to accomplish? Am I not seeing the forest for the trees?

    Thanks in advance for any ideas.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine months and years in Query (XP)

    Can you use something like the query in the attached database? It's not clear to me how, but that's up to you...
    Attached Files Attached Files

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Combine months and years in Query (XP)

    WOW! That was awesome! The [duration] column in the query was exactly what I was seeking. I don't know how you do it, but I am glad you do.

    THANKS HANS!

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Combine months and years in Query (XP)

    Hans,

    What you suggested worked great and allowed me to give him what he asked for. Of course, once he saw the results, it was not what he wanted. Using the query allowed showing that the problems were not just the first 12 months, but in fact roll through the second year. I have used the following SQL to accomplish what he asked for (i.e. first 12 months by quarter, then by year):

    SELECT tblDates.FirstName, tblDates.Date1, tblDates.Date2, DateDiff("yyyy",[Date1]+1,[Date2])+(Format([Date1]+1,"mmdd")>Format([Date2],"mmdd")) AS Years, (Month([Date2])-Month([Date1]+1)+12+(Day([Date2])<Day([Date1]+1))) Mod 12 AS Months, IIf([Years]<1,([Months]3)*3 & "-" & (([Months]3)*3+3) & " m",[Years] & "-" & ([Years]+1) & " y") AS Duration
    FROM tblDates;

    I have tried understanding how you were able to break the first year down by quarters, and then modifying the query to go out 24 months by quarter, then annually thereafter, but to no avail. Would you mind terribly modify the above query to accomplish the 24 instead of 12 month comparison. Hopefully, I can see by example what I am not understanding.

    Thank you.

    Ken

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine months and years in Query (XP)

    Does this do what you want?

    SELECT tblDates.FirstName, tblDates.Date1, tblDates.Date2, DateDiff("yyyy",[Date1]+1,[Date2])+(Format([Date1]+1,"mmdd")>Format([Date2],"mmdd")) AS Years, (Month([Date2])-Month([Date1]+1)+12+(Day([Date2])<Day([Date1]+1))) Mod 12 AS Months, IIf([Years]<1,([Months]3)*3 & "-" & (([Months]3)*3+3) & " m",IIf([Years]<2,"1 y " & ([Months]3)*3 & "-" & (([Months]3)*3+3) & " m",[Years] & "-" & ([Years]+1) & " y")) AS Duration
    FROM tblDates;

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Combine months and years in Query (XP)

    Hans,

    Almost. It does exactly what I want for 0-12 months (0-3m, 4-6m, 7-9m, and 10-12 m). For months 12-24, instead of "1 y 0-3 m" I was trying to get "13-15 m", and so on through 24 months, then continue with 3 y, 4 y, 5 y, etc.

    Does that make sense?

    Thanks,

    Ken

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combine months and years in Query (XP)

    The SQL then becomes

    SELECT tblDates.FirstName, tblDates.Date1, tblDates.Date2, DateDiff("yyyy",[Date1]+1,[Date2])+(Format([Date1]+1,"mmdd")>Format([Date2],"mmdd")) AS Years, (Month([Date2])-Month([Date1]+1)+12+(Day([Date2])<Day([Date1]+1))) Mod 12 AS Months, IIf([Years]<2,((12*[Years]+[Months])3)*3 & "-" & (((12*[Years]+[Months])3)*3+3) & " m",[Years] & "-" & ([Years]+1) & " y") AS Duration
    FROM tblDates;

    If you look at the expression for Duration:

    IIf(<font color=red>[Years]<2</font color=red>,<font color=448800>((12*[Years]+[Months])3)*3 & "-" & (((12*[Years]+[Months])3)*3+3) & " m"</font color=448800>,<font color=blue>[Years] & "-" & ([Years]+1) & " y"</font color=blue>)

    and analyze it part by part, you should be able to see how it works, and add further tweaks if desired.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Combine months and years in Query (XP)

    Hans,

    Thanks for the lesson. I shall put it to good use, and hopefully remember how to use in the future.

Posting Permissions

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