May 12 2005, 03:22 PM
I am building a query that shows how many hours it takes for us to ship a product.
I also flagged the number of units <=1.0.(Less than 24: IIf([SWO TIME]<=1,1,0))
How do I cover the range of 1.1 to 2.00 and so on.
My data looks like this: 5.94967592592729
May 12 2005, 04:53 PM
I just found out about a really cool function in Access:
here is something from the help
Partition Function Example(MDB)
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]
GROUP BY Orders.[CustomerID]
PIVOT Partition(Int([Freight]), 0, 1000, 50);
here is some SQL that just counts transactions in each partition:
SELECT Partition([amount],0,100000,1000) AS Amount_Category, Count(Transactions.tranID) AS Num_transcations
GROUP BY Partition([amount],0,100000,1000);