Solved Group by custom range of numbers. (1 Viewer)

[widemonk]

Hi All.
I have a query and one of the fields is an expression that returns a number.
This number represents number of days between 2 dates and is called ExprDaysDiff

The query draws fields from many different tables and the Report on which its based already includes a first level of grouping by one of these fields.

I want to create a secondary group based on ExprDaysDiff, based on the following ranges...
0-42
43-366
367-1461
1461+

My search results so far have said to start with 'SELECT iif(...' which implies I need to write the query directly in SQL.
Its already quite complicated (for me) and Im not comfortable manually editting the SQL.

There is another result that uses a VBA function but its a poor tutorial as the screenshots arent backed up with the full expressions.

Anyone able to help me please?

 

[CJ_London]

create a table to contain your ranges, include a PK or other unique number

then in your query add the table, but don't join it to the other tables (it is called a cartesian query). But use criteria

WHERE ExprDaysDiff between newTable.StartNum and newTable.EndNum

for ease, include an EndNum of say 10000 for your last range

and group on the newtable fields

Note if your existing query includes left or right joins then you cannot use a cartesian 'join' as well. To get over this, create another query using your existing query and newTable. Or it may be that you can apply the criteria further back down your query chain if you have one

Edit - just realised you say

one of the fields is an expression that returns a number

if this is a calculated field in table then the above may not work

 

[widemonk]

Hi CJ and thank you kindly.

Yes the query includes Left Joins, however a new unjoined query that calls the original and the new table works a treat.