Group By

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've been having a problem with grouping in my queries and
I finally figured out how to do what John Spencer
suggested ...

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

However, when I do this (either one) I get the following
message:

"You have written a subquery that can return more than one
field without using the EXISTS reserve word in the main's
query's FROM clause. Revise the SELECT statement of the
subquery to request only one field."

What in the world ?????

This is an explanation of my actual problem ...
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 appreciatedThat
Click to expand...
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")
 
Can you post the SQL of the Query that isn't working?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

I've been having a problem with grouping in my queries and
I finally figured out how to do what John Spencer
suggested ...

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

However, when I do this (either one) I get the following
message:

"You have written a subquery that can return more than one
field without using the EXISTS reserve word in the main's
query's FROM clause. Revise the SELECT statement of the
subquery to request only one field."
Click to expand...

SNIP
 
John:

Here it is ... Thanks so much for any help you can give.
This is driving me nuts!

SELECT (SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]) AS Expr1, [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 ((((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="03" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="15" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="26" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="37" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="40" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="50" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="52" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="54" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="55" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="60" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="61" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="62" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="65" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="70" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="90" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="97"));
-----Original Message-----
Can you post the SQL of the Query that isn't working?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

[email protected] said:
I've been having a problem with grouping in my queries and
I finally figured out how to do what John Spencer
suggested ...

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

However, when I do this (either one) I get the following
message:

"You have written a subquery that can return more than one
field without using the EXISTS reserve word in the main's
query's FROM clause. Revise the SELECT statement of the
subquery to request only one field."
Click to expand...

SNIP
.
Click to expand...
 
Using your originally posted SQL statement.

SELECT TBLGLdept.deptname,
IIF[TBLAccount Names].major<30,"Under30","Over30") as Category,
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 in ("03","15","26","37","40","50","52","54","55","60","61","62","65","70","90","97"
GROUP BY TBLGLdept.deptname, IIF[TBLAccount
Names].major<30,"Under30","Over30"), [date];

This may still not give you what you want, if your field named Date contains
full dates and you want to consolidate/report back by month. In that case, you
may need to replace Date with an expression such as Format([Date],"yyyy-mm") to
force the results to consolidate by year and month. on the other hand, you may
need to completely drop the Date field from your query.


John:

Here it is ... Thanks so much for any help you can give.
This is driving me nuts!

SELECT (SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]) AS Expr1, [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 ((((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="03" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="15" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="26" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="37" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="40" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="50" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="52" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="54" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="55" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="60" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="61" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="62" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="65" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="70" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="90" Or ((SELECT [Dept],
Sum(IIF([TBLAccount Names.Major]<30, CyTotal, 0)) as
Under30
FROM TBLGLDept
GROUP BY [Dept]))="97"));
-----Original Message-----
Can you post the SQL of the Query that isn't working?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

[email protected] said:
I've been having a problem with grouping in my queries and
I finally figured out how to do what John Spencer
suggested ...

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

However, when I do this (either one) I get the following
message:

"You have written a subquery that can return more than one
field without using the EXISTS reserve word in the main's
query's FROM clause. Revise the SELECT statement of the
subquery to request only one field."
Click to expand...

SNIP
.
Click to expand...
Click to expand...
 
Back
Top