Group by a range of values

[chancer1967]

Hello

I am stuck trying to group my data in a query, and am hoping for some help.


I have a table which I am interrogating. It details sales transactions, including all the usual stuff like product, quantity, price, customer, etc

A separate 'inventory master' table has category codes for each product code, so I am grouping the output by category

eg:
Category: 1
Sum of Quantity: 3

Which is all good and simple.


However, I then have a 'departments' table, which groups the categories by number range

eg
Department: 1
StartCategory: 1
EndCategory: 5

Department: 2
StartCategory: 6
EndCategory: 22

Department: 3
StartCategory: 23
EndCategory: 27


I want to group the data by Department, but cannot figure out how to link the departments table to the inventory master table, nor how to group by departments.

If the transaction data table happened to have a sales quantity of exactly 3 per category, then the final result should be

Department: 1
Sum of Quantity: 15

Department: 2
Sum of Quantity: 51

etc


I do not have control of the departments table - it is part of a separate system which I am interrogating. Categories can be added, and the department ranges can be changed, so whatever I do needs to be flexible.

I cant get my head around what to do, but hopefully it is fairly simple.



Thanks in advance for any advice

 

[gemma-the-husky]

really you need the category table to store a department ref as well, for each category

then this becomes trivial