Return Formatted Data

[PaddyIrishMan]

Hi,
I have a query showing Support response times in days.
It returns the amount of days for each record in the database based upon the DateDiff of two dates.
I'd like to be able to view this information based upon criteria,
I.e. I want to see a count of records between 0 And 50, between 50 And 100, 100 And 200 etc.. all in the same query. I'd also like to chart the information, again using bars for each criteria - bar for count between 0 And 50 and so on.
I just can't seem to get the query right.
Any pointers??

Thanks in advance,
Patrick.

 

[dcx693]

I can't help you with the charting part of your question, but I can make some suggestions to help you get on track with the query.

To get the data into the ranges you'd like, you can create a custom function in a query to group the data. Have it look for the DateDiff amount, then assign it into those categories using a Select Case statement. You can then run a totals query, grouping on the calculated category column.

If you had equal-sized categories, I'd suggest making it really easy and using the Partition() function, but that's not the case here. Between 0 and 50 there are 51 days. Between 100 and 50 there are 51 days, but between 100 and 200 there are 100 days (inclusive).

 

[PaddyIrishMan]

Thanks for your suggestions dcx,

If you had equal-sized categories, I'd suggest making it really easy

Like music to my ears - making it really easy is right up my street, I can be pretty flexible on the partitions - could you elaborate some more on how you'd use the Partition Function?

Thanks,
Patrick.

 

[dcx693]

No problem. The Partition function syntax is: Partition(number, start, stop, interval)

The first parameter is what you want to calculate. The second is where you want to start the first number at, the third is where you want to put the last number at, and the interval is the number of intervals you want to group by.

For example, if you want to group from 0-49, end it at 199, and base it on today's date minus the ResponseDate, you'd do this:Partition(Now()-[ResponseDate],0,199,50)

That will break your data into groups of 0-49, 50-99, 100-149, and 150-199.

 

[PaddyIrishMan]

Looks like just what I'm after.
I'll give it a go.

Thanks again,
Patrick.