Grouping results

[IgorM]

Hi

How can I query a table with the following data:
CreditorName, CreditorID, Value
so the date is summed up by Value (there are multiple entries for one
Creditor) and grouped in the following way:
from 0 to 5000
from 5001 to 10000
from 10001 to 1000000
more than 1000000

So I don't whant to see sums of the value column. I just want to see the
Creditor name and ID within the give value groups.

Thanks for any help

IgorM

 

[Rob Wills]

Use the [Iif] statement... if you're using the GUI to build your SQL you
will need to build the totals and "group by" the relevant categories - but
the fields where you're calculating values will need to be listed as
expressions....

This is a small example:

SELECT CreditorID, CreditorName, sum(iif(Value < 5001,1,0)) as [0 to 5000],
Sum(iif(Value > 5000,iif(Value<10001,1,0),0)) as [5001 to 10000]
FROM [TableName]
GROUP BY CreditorID, CreditorName

 

[John Spencer]

TRY the following

SELECT CreditorID, CreditorName
, IIF(SUM([Value])<=5000,"Group1", IIF(Sum([Value])<=10000,"Group2",
IIF(Sum([Value])<=1000000,"Group3",IIF(Sum([Value])>1000000,"Group4"))))
FROM [YourTable]
GROUP BY

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dale Fye]

Or, you could create a grouping table with fields:
GroupValue From To SortOrder
"< 5000" -1 5000 4
"5001 to 10000" 5000 10000 3
"10001 to 100000" 100000 1000000 2
"> 1000000" 1000000 999999999 1

Then, in your query do something like:

Select GroupValue, CreditorID, CreditorName
FROM (SELECT CreditorID, CreditorName, Sum(Value) as SumVal
FROM yourTable
GROUP BY CreditorID, CreditorName) as Credit,
tbl_Grouping
WHERE Credit.SumVal > tbl_Grouping.From
AND Credit.SumVal <= tbl_Grouping.To
ORDER by tbl_Grouping.SortOrder, CreditorName

This method allows you to change the groupings relatively easily, as opposed
to having to modify the iif( ) functions in all of the queries where you want
to use this functionality.