Group By

M

Margaret

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];


..
 
J

John Spencer (MVP)

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")
 
M

Margaret

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")

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];

.
Click to expand...
.
Click to expand...
 
Top