Query which needs different bandings 1

[Craig9999]

SELECT SUM(Field14) AS Amount, 1 AS [Band ID]
FROM orders
WHERE (((Field14) Between 0.00 And 9999.99))

UNION SELECT SUM(Field14) AS Amount, 2 AS [Band ID]
FROM orders
WHERE (((Field14) Between 10000 And 39999.99))

UNION SELECT SUM(Field14) AS Amount, 3 AS [Band ID]
FROM orders
WHERE (((Field14) Between 40000 And 99999.99))
;

The above SQL query works 'OK' however the bands are hard coded.

My boss!! would like me to create a new table which has three colums
Band ID LowerBand UpperBand

So as the user can change and add bandings at 'The drop of a hat'

The other table has the amounts in field 14 however there is no reference to a group banding.

So I'm after creating a temp table in which the amounts are allocated to a band and the I can sum the different bands.

Simple, Easy, I hear you say.

So why have I just spend 4 Hours going around in circles.
[PC3]
Any ideas? / Or any better ideas

Please Help

Thanks
Craig

 

[mpastore]

Try:

SELECT Sum(a.field14) AS Amount, b.[band id]
FROM orders AS a, mybands AS b
WHERE (((a.field14) Between .[LowerBand] And .[UpperBand]))
GROUP BY b.[band id]
ORDER BY b.[band id];


Mike Pastore

Hats off to (Roy) Harper

 

[mpastore]

Sorry, those TGML control characters screwed me up again. I am assuming a table name of mybands:

SELECT Sum(a.field14) AS Amount, [band id]
FROM orders AS a, [mybands]
WHERE (((a.field14) Between [LowerBand] And [UpperBand]))
GROUP BY [band id]
ORDER BY [band id];


Mike Pastore

Hats off to (Roy) Harper

 

[Craig9999]

mpastore

Mike,

You are the Man!!! Hooooooray

I could run around the room with delight.

Thanks for the timely, helpful and brillant post.

The query is working on the database.


Thanks

Have a star

Craig