Group by non-standard expression. (1 Viewer)

George Bowyer

Registered User.
Local time
Today, 10:39
Joined
May 17, 2004
Messages
50
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

AWF VIP
Local time
Today, 02:39
Joined
Jul 2, 2005
Messages
13,826
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

Registered User.
Local time
Today, 10:39
Joined
May 17, 2004
Messages
50
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

AWF VIP
Local time
Today, 02:39
Joined
Jul 2, 2005
Messages
13,826
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

Immoderate Moderator
Staff member
Local time
Today, 03:39
Joined
Feb 28, 2001
Messages
27,001
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

Code:
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:39
Joined
Feb 19, 2002
Messages
42,981
Partition() works as long as you have an even division. If you have variable ranges or if you want some flexibility to easily change a range, the simplest solution is to use a table. That is also the most flexible since it is the easiest to change.

Start, Stop, Group
1, 3, A
4, 7, B
8, 10, C
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:39
Joined
Feb 28, 2001
Messages
27,001
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.
 

Users who are viewing this thread

Top Bottom