Rolling up records into bands/sets 5

[captainquigers]

How would I roll up records based upon which band a value lies in?

The data looks like this;

Field1 Field2
1 48.25
2 12.68
2 98.16
7 12.36
8 67.46
12 46.64
15 45.89

And I would like like the query to output;

Field1 Field2(sum) Field2(avg)
1-5 159.09 53.03
6-10 79.82 39.91
11-15 95.53 47.77
16+ 0 0

There are 30 bands that the value could fall into so a nested IF statement with a BETWEEN clause is way too big!

I also thought about using a lookup table, but as the value can be between -50,000.00 and 250,000.00 this table would also be too big.

Any help would be greatly apprecited.

Cheers,

Captain

 

[Crowley16]

eh, if you want to use a query to do this, then you'll have to specify the boundaries somehow...

to make it clear, you can use a large union query...

Crowley - as in like a Crow

 

[GHolden]

Hi,

You could try something like this...

If you use integer division on field1 and group by it e.g.

SELECT SUM(field2) AS SumOf2, AVG(field2) AS AvgOf2
FROM table
group by ((field1 - 1)\5);

This should total/average of each group of 5 records, it won't give you the 1-5, 6-10 etc for column one though :-(

Might be of some help though....


There are two ways to write error-free programs; only the third one works.

 

[dhookom]

I like to maintain my groups/bands in data rather than in code or complex expressions. This involves creating a table of bands
[Blue][tt]
tblBands
BandMin BandMax BandTitle
1 5 1-5
6 10 6-10
11 15 11-15
16 10000 16+
[/tt][/blue]
You can add this table to your queries and set the criteria of your field/column to:
Between [BandMin] And [BandMax]

This allows you to easily change your mind regarding your bands. Just change data rather than expressions.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[OhioSteve]

The keys to solving this puzzle are the mod() and int() functions. This is the full solution.

1) Create a table called "testTable". It should have two fields: testID and testAmount. Both should be long integers. There should be 20 records. TestID should start with one and go to 20. testAmount can have any amounts you like. Its easiest to see the solution if the amounts are small.

2) create a query called "Q1" and paste this sql into it:

SELECT
testTable.testID,
testTable.testAmount,
Int([testID]/5) AS a,
[testID] Mod 5 AS b,
IIf(=0,([a]-1)*5+1,[a]*5+1) AS c,
IIf(=0,[testID],[a]*5+5) AS d
FROM testTable;

3) Create a query called "Q2" and paste this sql into it:

SELECT
[c] & " - " & [d] AS [group boundaries],
Sum(Q1.testAmount) AS [group total],
Avg(Q1.testAmount) AS [group average]
FROM Q1
GROUP BY Q1.c, Q1.d
ORDER BY Q1.c, Q1.d;

 

[OhioSteve]

I mispoke in my last post. Mod is an operator not a function.

 

[cmmrfrds]

There is a function called "partition" which may work for what you need. Here is a simple example using age.

SELECT DISTINCTROW Partition([age],0,65,5) AS Range, Count([age]) AS [Count]
FROM Categorys
GROUP BY Partition([age],0,65,5);

 

[PHV]

Have you tried something like this ?
SELECT (1+5*((Field1-1)\5)) & "-" & (5+5*((Field1-1)\5)) AS RangeField1, Sum(Field2) AS SumOf2, Avg(Field2) AS AvgOf2, ((Field1-1)\5) AS SortField
FROM theTable
GROUP BY (1+5*((Field1-1)\5)) & "-" & (5+5*((Field1-1)\5)), ((Field1-1)\5)
ORDER BY ((Field1-1)\5);

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

You folks sure like to write and maintain expressions. I agree that there are several ways to accomplish this but a complex expression like this would never make it into an application that I would create. From past experience, I know that as soon as I would create the expression, some PHB (Pointed Haired Boss) would want to see different bands.

At the very least, create a single user-defined function that does the partitioning/banding. Save the function in a public module named: "basBusinessRulesThatWillChangeOverTime".
When (not if) you business rules change, you have one place to make changes. You would be able to call the function from any query or control source without re-typing an expression.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PHV]

dhookom, I obviously agree.
cmmrfrds, star for you for the recall of the Partition function.
So the simpler query is:
SELECT Partition(Field1,1,1000,5) AS RangeField1, Sum(Field2) AS SumOf2, Avg(Field2) AS AvgOf2
FROM theTable
GROUP BY Partition(Field1,1,1000,5);

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244