Group dates by 30 60 and 90 day intervals in report 3

[Gilbertjoe]

I have a query (aging query)that runs with customer name, product, and received date (with criteria of <now()-30 and not is null). I need my report(aging report) that is based on that query to take all of those dates greater than 30 days old from now and group them in older than 30 days, older than 60 days, older than 90 days, older than 120 days intervals. Do I need to run more than one query and put these into subreports or is there an expression I can write to give me this data in the query I have?
Example:
customer: John doe
products over 30 days old
widget
widget deluxe
products over 60 days old
widget basic
widget pro edition
etc. etc.
Any help would be appreciated. Thanks.

 

[MichaelRed]

I would use a series of queries and link the results together. Send me a &quot;sample&quot; (e-mail, please) table with some of each group and I will generate a (sample?) query to do it. Once you see the sample, you should be able to use the approach almost anywhere, for groupings.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[scking]

Build a query with your groupings and then use the following concept. In the field put something like:

TotalCount: Count(IIf(Mid$([LetterNbr],1,3) Is Not Null,1))

In the Total field put select Expression.

This will count each [LetterNumber] where the first three characters are not null. When that is the case it will use the 1 to could the occurance. This same thing can be used with any type of expression to count a record if it meets the expression criteria. You can also keep count of any type values as long as the Grouping is the same. I originally used this to keep track of each record date that fell withing a certain date range. i.e. FirstQtr: Count(IIf(DateStamp > Date and < DateAdd(&quot;m&quot;, Date, 3),1)
The DateAdd function is probably off because I'm doing it from memory but it should give you the idea.

Steve King

 

[MichaelRed]

At least the order is wrong. Some other parts are open to interpertation. If you want the &quot;First Quarter&quot;, the reference date should be specified, not just left as &quot;Date&quot;.

FirstQtr: Count(IIf(DateStamp > Date and < DateAdd(&quot;m&quot;, 3 Date),1)

'________________________________________________________

DaysAged: DateDiff(&quot;d&quot;,(Format([RcvDt], &quot;Short Date&quot, Format(Now(), &quot;Short Date&quot

Age0to30: [DaysAged] Between 0 and 30
Age31to60: [DaysAged] Between 31 and 60
Age61to90: [DaysAged] Between 61 and 90
AgeG_T_91: [DaysAged] >= 91

The above 'adds' five fields to the existing query. (asuming the correct interpertation. The 'name' before the &quot;:&quot; is the field name. the remainder is the criteria)

The &quot;DaysAged&quot; field simply returns the days between the RcvDt (&quot;received date&quot and the current date.

Each of the Age* fields will be set to &quot;True&quot; (-1) if the ageing falls into the 'category'.

Now, do an additional query to count the Age* fields (or do a Abs(Sum(Age*)).

'________________________________________________________

Post a 'small' sample set of data w/ field names. I will look at doing a couple of soloutions.




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[scking]

Great implementation Michael. It's clean, has a good naming convention, and very clear. Very elegant - something I strive for but don't always succeed with.

Steve King