Group By

  • Thread starter Thread starter margaret
  • Start date Start date
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];
 
And Answered (I HOPE!!!) in queries group.
Sorry ... I meant to post this in "Queries" ...
-----Original Message-----
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...
 
Back
Top