2 Group By clauses

[eaddi]

Hello,

I am still a newbie with Access. But I want to use to Group by clauses or a Group by and Order By cloause together. Below is what I am striving for. I am using a Union query with a date parameter. When I try to add the Order By Clause I get the error "You tried to execute a query that doe snot include the specified expression 'rate_type' as part of an aggregate function. Please help or let me know if more info is needed.

System Rate Type ....
Name 1 Fixed ....
Name 1 Variable ....
Name 2 Fixed ....
Name 2 Variable ....


SELECT system, rate_type, Sum(Loan_Query.Volume)/1000 AS YTD_Volume, Sum(volume*war)/Sum(volume) AS YTD_WAR, Sum(volume*warr)/Sum(volume) AS Avg_WARR, Sum(volume*promotion)/Sum(volume) AS YTD_PromotionCycle, Sum(volume*wat)/Sum(volume) AS YTD_WAT, Sum(spread*volume)/Sum(volume) AS YTD_Spread
FROM Loan_Query
GROUP BY Loan_Query.system
ORDER BY rate_type;

 

[boblarson]

you have to include your rate type in the Group By

SELECT system, rate_type, Sum(Loan_Query.Volume)/1000 AS YTD_Volume, Sum(volume*war)/Sum(volume) AS YTD_WAR, Sum(volume*warr)/Sum(volume) AS Avg_WARR, Sum(volume*promotion)/Sum(volume) AS YTD_PromotionCycle, Sum(volume*wat)/Sum(volume) AS YTD_WAT, Sum(spread*volume)/Sum(volume) AS YTD_Spread
FROM Loan_Query
GROUP BY [system], rate_type
ORDER BY system, rate_type;


If you want to order by rate_type first, no problem


SELECT system, rate_type, Sum(Loan_Query.Volume)/1000 AS YTD_Volume, Sum(volume*war)/Sum(volume) AS YTD_WAR, Sum(volume*warr)/Sum(volume) AS Avg_WARR, Sum(volume*promotion)/Sum(volume) AS YTD_PromotionCycle, Sum(volume*wat)/Sum(volume) AS YTD_WAT, Sum(spread*volume)/Sum(volume) AS YTD_Spread
FROM Loan_Query
GROUP BY [system], rate_type
ORDER BY rate_type, system;

 

[eaddi]

Thank you so much. I knew it had to be something simple.