I bet this is actually quite a simple query....

likewise. tnx for the partition function!! i'd never heard of it before.
 
tnx for the partition function!! i'd never heard of it before.

...makes me wonder what else there is out there that we're missing?!

Bob
 
Axess Newbie,

Use a Public Function:

Code:
Public Function GroupPeople (strAge As Variant) As String

If Not IsNull(strAge) Then
   GroupPeople = "Null"
   Exit Function
End If 

If Not IsNumber(strAge) Then
   GroupPeople = "Invalid"
   Exit Function
End If   

Select Case CLng(strAge)
   Case < 16
     GroupPeople = "<16"
   Case 16 To 20
     GroupPeople = "16-20"
   Case 21 To 25
     GroupPeople = "21-25"
   Case 26 To 30
     GroupPeople = "25-30"
   Case 31 To 35
     GroupPeople = "31-35"
   Case 36 To 40
     GroupPeople = "36-40"
   Case 41 To 45
     GroupPeople = "41-45"
   Case 46 To 50
     GroupPeople = "46-50"
   Case Else
     GroupPeople = ">50"
   End Select
End Function


Code:
Select GroupPeople([age]), Count(*)
From   YourTable
Group By GroupPeople([age])

Wayne
 
Thanks Wayne,

I know it's possible. What I'm trying to do is to determine if it's somehow possible to use/abuse/modify the Partition() function without all of the situation-specific code.

Appreciate your input.

Bob
 
Bob,

Since a few of the caveats are that the field may be Null or not a number
at all, I don't think it's possible to use the Partition function.

Wayne
 
Thanks Wayne,

What I'm trying to do is to get around the situation-specific (althogh quite accurate) monster you've posted. Check this out: http://www.access-programmers.co.uk/forums/showthread.php?t=159751. Obviously, from the responses already received, most folks haven't ever even heard of the Partition() function.

Do you see a way to use/modify the SQL Server solution?

Bob
 
Last edited:
wayne, a small typo i think:

Code:
If [COLOR="Red"]Not[/COLOR] IsNull(strAge) Then
   GroupPeople = "Null"
   Exit Function
End If
 
Wazz,

You're right.

In defense, I didn't test the code and it was just to show the principle.

Good catch,
Wayne
 

Users who are viewing this thread

Back
Top Bottom