If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Grouping results
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 |
#2
|
|||
|
|||
Grouping results
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(Value10001,1,0),0)) as [5001 to 10000] FROM [TableName] GROUP BY CreditorID, CreditorName |
#3
|
|||
|
|||
Grouping results
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 IgorM wrote: 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 |
#4
|
|||
|
|||
Grouping results
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. ---- HTH Dale "IgorM" wrote: 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 . |
Thread Tools | |
Display Modes | |
|
|