# Thread: Combine months and years in Query (XP)

1. ## 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. ## 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...

3. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•