Grouping numbers

[sislman01]

Hi,

I would like to know how i can group numbers into bands.

e.g

Col 1

1
2
3
4
5
6

How do I group the above into groups of i.e. 1 - 3 and 4 - 6, or group in regular intervals like 1-2, 3-4, 5-6?

Regards

 

[macca the hacke]

2 ways spring to mind - first is to have a table that has 2 columns - 1 has actual number, and other has group it belongs to.

second would be using iif statements (ie iif([fieldname]>=1 and <=3,"1-3",etc)

 

[allan57]

Partition function

Hi, I thought this might be of interest to you:-

Source of Info MS Help
You can use the Partition function in the query design grid as well as in the SQL view of the Query window. In the query design grid, you
can use the Partition function in a calculated field or to specify criteria for a select query.
The following example shows how you can use the Partition function to create a calculated field that lists how many records fall into
each specified range. Suppose you have an Orders table that contains a Freight field. In the Query window, create a new Totals query
by adding the Orders table and clicking on the Totals button in the Query command bar. Drag the Freight field to the first Field cell on
the query design grid, and set the value of the Total cell to Count. In another field cell, enter the following expression.

Range: Partition([Freight], 0, 1000, 50)

Set the Total cell below this field to Group By, and run the query. The Partition function returns eleven ranges (0:99, 100:199, 200:299,
and so on). The query shows the number of orders with freight charges falling into each range.
The next example shows how you can use the Partition function in the SQL view of the Query window. It creates a crosstab query that
evaluates a Freight field in an Orders table. It calculates the number of orders for each customer for which freight cost falls within one of
several ranges. The ranges are defined by the arguments to the Partition function: start = 0, stop = 1000, interval = 50.

Enter the following expression in SQL view. When you run this query, each range will appear as a column heading.

TRANSFORM Count(Orders.[OrderID]) AS [CountOfOrderID]
SELECT Orders.[CustomerID]
FROM Orders
GROUP BY Orders.[CustomerID]
PIVOT Partition(Int([Freight]), 0, 1000, 50);

 

[sislman01]

Thanks guys,

I used this sql query to group the column by equal bands.

SELECT Count(Bluetest.[Holiday Duration in Days]) AS [CountOfHoliday Duration in Days], Partition([Holiday Duration in Days],0,1000,50) AS Range
FROM Bluetest
GROUP BY Partition([Holiday Duration in Days],0,1000,50)

If I wanted to group in un-equal bands i.e.

3
5
10
17
24

How could I do this?