Counting no. of records in a few range

[matticool]

Hi i want to count the number of records in a few range. i have a field with [current date], [dateordered] and [DaysInBetween].

So these are the few range i have, less than one week, 2weeks to 1 month, 1-3months, 4-6months and more than 6 months. i wanna to count the records in [DaysInBetween] and fall within these ranges.

eg
less than one week.......10
2weeks to 1 month.......5
1-3months..................7
4-6months..................2
more than 6 months.....15

How can i reproduce these results by query? Thanks!

 

[plog]

When you talk about your fields, is that a table or a query? If it's a table, I get to yell at you for storing unnecessary data. You shouldn't store [current date] nor [DaysInBetween] because those can be derived from the system ([current date]=Date()) and from other data ([DaysInBetween]=DateDiff("d", [dateordered], Date())).

On to your actual issue: The best way to handle this is to build a custom function in a Module. This is the code you would put in your Module:

Function GetDateRange(odate)
    ' determines which date range to put data in based on odate
ret = "Invalid Date"
ddif = DateDiff("m", odate, Date)
    ' holds difference in days between order date and current date
 
If (ddif < 7) Then ret = "Less Than 1 Week"
If ((ddif >= 7) And (ddif < 14)) Then ret = "1 Week to 13 Days"
 
'  Add more if statements here based on the Data Ranges you want

    
GetDateRange = ret
End Function

The above code isn't complete--I just typed 2 cases--be sure to fill in the appropriate cases for all the ranges you want.

For the query, this is the SQL:

SELECT GetDateRange([dateordered]) AS DateRange, COUNT([dateordered]) AS RangeTotal FROM  YourTableNameHere GROUP BY GetDateRange([dateordered]);

Be sure to replace 'YourTableNameHere' with the name of the table or query that contains the field [dateordered].