Group by Size Range

  • Thread starter Thread starter Charles D Clayton Jr
  • Start date Start date
C

Charles D Clayton Jr

I have a crosstab query that will return the sizes of pipe and the
quanity used by location. The "size" is the row (Group by), "pipe" is
the value (Count), "location" is the column (Group by). It looks sort
of like this (made up data. BPD, APD, etc are locations):

Size Total BPD APD BDD ADD
..5 4 3 1
..75 8 1 5 1 1
1 10 3 3 3 1
2 25 20 1 2 2
3 40 10 10 10 10
4 4 1 1 1 1
5 30 10 5 10 5
6 100 25 25 25 25
8 75 50 20 2 3
10 200 100 50 25 25
16 10 2 2 2 4

What I need to know is this, is it possible to group by a size range
rather than just size? Instead of 11 sizes, I need 3 groups (less
than 2", 2-8" and greater than 8").

Thanks,

Charles D Clayton Jr
 
Type a calculated field such as this into the Field row of your query:
SizeRange: Switch([Size] < 2, 1, [Size] <= 8, 2, True, 3)
 
Or, you might want to consider creating ProductSizeGroup table that
contains a productID, Size, and SizeGroup field.

You could then join this table to the main table in your query by
productID and Size, then use the SizeGroup field and the Row column in
you crosstab query. If all you have is one product or all of your
products are pipes, then you could just do this with Size and
SizeGroup fields.

This technique would be more dynamic than putting it in your query
because all you would have to do is change the SizeGroup field in you
table to move a pipe from one group to another. If you hard code it
into a query, then when the sized that go into a group change, you
will have to find all references to that code, not an easy task.


--
HTH

Dale Fye


message I have a crosstab query that will return the sizes of pipe and the
quanity used by location. The "size" is the row (Group by), "pipe" is
the value (Count), "location" is the column (Group by). It looks sort
of like this (made up data. BPD, APD, etc are locations):

Size Total BPD APD BDD ADD
..5 4 3 1
..75 8 1 5 1 1
1 10 3 3 3 1
2 25 20 1 2 2
3 40 10 10 10 10
4 4 1 1 1 1
5 30 10 5 10 5
6 100 25 25 25 25
8 75 50 20 2 3
10 200 100 50 25 25
16 10 2 2 2 4

What I need to know is this, is it possible to group by a size range
rather than just size? Instead of 11 sizes, I need 3 groups (less
than 2", 2-8" and greater than 8").

Thanks,

Charles D Clayton Jr
 
Thanks for the comments. While waiting for everyone's answers, I did
some more searching and came across the idea of writing a function to
accomplish the task.

Function SizeRange(SizeR As Variant) As String
' The Function will group all the sizes of the pipe into 3
categories
Select Case SizeR
Case Is < 2
SizeRange = "1. <2"""
Case 2 To 8
SizeRange = "2. 2"" - 8"""
Case Is > 8
SizeRange = "3. >8"""
Case Else
SizeRange = "4. Unknown"
End Select
Exit Function
End Function

Then I call it from the query with a calculated field:
"Size Range: SizeRange([size])" and that seems to work fine (at least
for now).

I have never heard of the "Switch" function and I will have to look
into it a little closer. It sounds like it would have been a quicker
fix than my solution.

Blessings,

Charles D Clayton jr
 
Back
Top