Group By

From: Margaret (anonymous_at_discussions.microsoft.com)
Date: 07/30/04 

Date: Fri, 30 Jul 2004 12:24:54 -0700

 

I know this should be simple and I'm having a brain freeze
with this, but I tried your first suggestion (actually
Marsh suggested the same thing for me on the reports area)
and I can't figure out where I'm supposed to put the "Sum"
statement. Am I supposed to put it on the criteria of the
[dept] field, because if so, I already have criteria
there. And, when I do try that (foregoing my previous
[dept] criteria) I'm getting a statement that says it's
too complex.

If I do the second suggestion,I'm still confused as to
where to put the IIf statement.

I know this should be easy, but as I said, I think I'm
having a brain freeze and making this more difficult than
it is.

Thanks for any help.
>-----Original Message-----
>That would look something like:
>
>SELECT [Dept],
> Sum(IIF([Major]<30,CyTotal,0)) as Under30,
> Sum(IIF([Major]>=30,CyTotal,0)) as Over30
>FROM YourTable
>GROUP BY [Dept]
>
>An alternative, would be to build a category using an IIF
statement and Group by that
>
>SELECT [Dept],
> IIF(Major<30,"Under30","Over30") as Category,
> SUM(CyTotal) as Total
>FROM YourTable
>GROUP BY [Dept],
> IIF(Major<30,"Under30","Over30")
>
>
>Margaret wrote:
>>
>> I'm having trouble with "Group By" Queries.
>>
>> I need to write a report that lists major > 30 and then
>> major < 30. So if I had the following information
>>
>> dept Major CYTotal
>> 10 15 100
>> 10 30 125
>> 15 25 100
>> 15 30 100
>> 15 31 450
>> In my report I would have
>>
>> Dept Major > 30 Major < 30
>> 10 100 125
>> 15 100 550 (major 30 & 31 added
together)
>>
>> I know I need to do a group by and group all majors
above
>> 30 and all majors below 30 together, however, I 'm
>> confused as to how to separate them. Here's the code I
>> have so far. Any help would be greately appreciated.
>>
>> SELECT [TBLAccount Names].major, TBLGLdept.deptname, Sum
(Nz
>> ([cy1],0)+Nz([cy2],0)) AS CYTotal, Sum((Nz([ly1],0)+Nz
>> ([ly2],0))) AS LYTotal, Sum((Nz([bcy1],0)+Nz([bcy2],0)))
>> AS BCYTotal, [date] AS [Month]
>> FROM ([TBLAccount Names] INNER JOIN (TBLglfinance INNER
>> JOIN [TBLAccount Breakdown] ON TBLglfinance.ACCOUNT =
>> [TBLAccount Breakdown].ACCOUNT) ON ([TBLAccount
>> Names].ACCOUNT = [TBLAccount Breakdown].ACCOUNT) AND
>> ([TBLAccount Names].ACCOUNT = TBLglfinance.ACCOUNT))
INNER
>> JOIN TBLGLdept ON [TBLAccount Names].DEPT =
TBLGLdept.dept
>> WHERE (((TBLGLdept.dept)="03" Or (TBLGLdept.dept)="15"
Or
>> (TBLGLdept.dept)="26" Or (TBLGLdept.dept)="37" Or
>> (TBLGLdept.dept)="40" Or (TBLGLdept.dept)="50" Or
>> (TBLGLdept.dept)="52" Or (TBLGLdept.dept)="54" Or
>> (TBLGLdept.dept)="55" Or (TBLGLdept.dept)="60" Or
>> (TBLGLdept.dept)="61" Or (TBLGLdept.dept)="62" Or
>> (TBLGLdept.dept)="65" Or (TBLGLdept.dept)="70" Or
>> (TBLGLdept.dept)="90" Or (TBLGLdept.dept)="97"))
>> GROUP BY [TBLAccount Names].major, TBLGLdept.deptname,
>> [date];
>>
>> .
>.
>
.