Group by non-standard expression.

[George Bowyer]

I have records in a table which have a "Score field", with a score from 1 - 10.

I want to run a report from a select query that groups the records as Scores 1-3, 4-7 and 8-10.

Is this possible - and do I do it through the query or the report grouping?

Or do I have to have three queries?

Many thanks?

George

 

[RuralGuy]

How about adding a calculated field to the query that uses something like Switch() to group the records for you to group on?

 

[George Bowyer]

Yep, that's done it. Thanks.

"Switch" is a new function to me

At the moment my switch function has 10 clauses. Can I use a range of some sort to shorten it?

eg: switch([fldscore] = 1 to 3, "A", etc.

 

[RuralGuy]

I'm not really sure but I would try 1-3 rather than 1 to 3. You could also create a Public function in a Standard module that uses the Select Case structure if you wanted.

 

[The_Doc_Man]

You might try the Partition function and cheat just a little.

https://support.office.com/en-us/article/partition-function-1a846a33-60c7-4371-8e77-c94278274dc5

So you have 1-3, 4-7, and 8-10. But read up on Partition and then consider

SELECT whatever FROM wherever WHERE something or another 
GROUP BY Partition( fldscore, 0, 11, 4 );

Normally you expect even-sized partitions - but as it happens, with only three partitions, if you flex your starting number and ending number to make the range appear to be 12 (never mind that 0 and 11 will never occur), Partition might do the job for you.

The possible returns for Partition for this case would be ":-1", "0:3", "4:7", "8:11", and "12:" - but of course the partition function doesn't "know" that the extremes won't exist. And you don't have to actually show the partitioned range if you don't want to.

 

[NauticalGent]

Learn something new everyday...

 

[The_Doc_Man]

Between having a SWITCH function or a PARTITION function or using a table-lookup, it is obvious that there are at least 3 ways to skin this cat. You COULD have also used RuralGuy's suggestion for a function to return some range indicator. So that's a fourth method. Somewhere in there, you should find something that appeals to you.