group by error

[namas]

When I do groupby gl_acct, the following error shows up.
Can anyone find out why? Thanks.

You tried to execute a query that does not include the specified expression
'gl_acct' as part of an aggregate function.

SELECT IFS_gl_balance.gl_acct, IFS_gl_master.gl_abbrev_desc, (SELECT Sum(IFS_gl_balance.gl_balance_amt)
FROM IFS_gl_balance as IFS_gl_balance1, IFS_fd_master as IFS_fd_master1
WHERE IFS_gl_balance1.gl_id = IFS_gl_balance.gl_id
AND IFS_gl_balance1.gl_year = 2006
AND IFS_gl_balance1.gl_period < 1
AND IFS_gl_balance1.gl_period >= 0
AND IFS_fd_master1.fd_id = IFS_gl_balance1.fd_id) AS GL_BEGBAL, (SELECT Sum(IFS_gl_balance.gl_balance_debit)
FROM IFS_gl_balance as IFS_gl_balance2, IFS_fd_master as IFS_fd_master2
WHERE IFS_gl_balance2.gl_id = IFS_gl_balance.gl_id
AND IFS_gl_balance2.gl_year = 2006
AND IFS_gl_balance2.gl_period <= 1
AND IFS_gl_balance2.gl_period >= 1
AND IFS_fd_master2.fd_id = IFS_gl_balance2.fd_id) AS GL_DEBITS, (SELECT Sum(IFS_gl_balance.gl_balance_credit)
FROM IFS_gl_balance as IFS_gl_balance3, IFS_fd_master as IFS_fd_master3
WHERE IFS_gl_balance3.gl_id = IFS_gl_balance.gl_id
AND IFS_gl_balance3.gl_year = 2006
AND IFS_gl_balance3.gl_period <= 1
AND IFS_gl_balance3.gl_period >= 1
AND IFS_fd_master3.fd_id = IFS_gl_balance3.fd_id) AS GL_CREDITS
FROM IFS_gl_master INNER JOIN (IFS_gl_balance INNER JOIN IFS_fd_master ON IFS_gl_balance.fd_id = IFS_fd_master.fd_id) ON (IFS_gl_master.fd_company = IFS_fd_master.fd_company) AND (IFS_gl_master.gl_id = IFS_gl_balance.gl_acct)
WHERE (((IFS_gl_balance.gl_year)=2006) AND ((IFS_gl_balance.gl_period)<=1))
GROUP BY IFS_gl_balance.gl_acct, IFS_gl_master.gl_abbrev_desc
HAVING (((IFS_gl_balance.gl_acct)="1000"))
ORDER BY IFS_gl_balance.gl_acct, IFS_gl_master.gl_abbrev_desc;

 

[kjv1611]

Include 'gl_acct' in your GROUP BY clause. That should fix it - I've made this mistake before, myself. [WINK]

 

[lespaul]

Here's a general explanation of the GROUP BY clause:

[tt]
An aggregate function performs based on all the other fields in your select.

If you have a table like:

Salesman SaleDate Amount
JR 1/1/2006 $500.00
EM 1/1/2006 $250.00
JR 1/1/2006 $100.00
EM 1/2/2006 $101.00
JR 1/2/2006 $75.00
JR 1/2/2006 $100.00

and you want to sum the sales for the month for EACH salesman:

SELECT Salesman, SUM(Amount) FROM TableName GROUP BY Salesman

the results would be:

JR $775.00
EM $351.00

If you want to know how much per day:

SELECT SalesDate, SUM(Amount) FROM TableNmae GROUP BY SalesDate

the results would be:

1/1/2006 850.00
1/2/2006 276.00

If you want by Salesman, by Date:

SELECT Salesman, SalesDate, SUM(Amount) FROM tableName GROUP BY Salesman, SalesDate ORDER BY Salesman, SalesDate

EM 1/1/2006 250.00
EM 1/2/2006 101.00
JR 1/1/2006 600.00
JR 1/2/2006 175.00[/tt]

Every field that is in your select clause that is NOT an aggregate (SUM, COUNT, AVG, etc.), must be listed in the GROUP BY clause.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Golom]

There may be another problem as well. Looking at the three SELECT statements that are generating the fields, each of them has

SELECT Sum([red]IFS_gl_balance[/red].gl_balance_amt) ...

because you have aliased [red]IFS_gl_balance[/red] within each of the sub-queries, that actually refers to the instance of that table in the outer query and not to the ones in the sub-queries which are called
- IFS_gl_balance[red]1[/red]
- IFS_gl_balance[red]2[/red]
- IFS_gl_balance[red]3[/red]
respectively.

Is that what you really intend?

 

[lespaul]

Also just as some more query information....Access is notorious for using the HAVING clause incorrectly. here's a primer on the HAVING clause:

[tt]
you only need to use a HAVING clause to filter on an aggregate function. Say you wanted to see total orders by customer:

SELECT Customer, SUM(OrderTotal) FROM Orders GROUP BY Customer

this will return:

1 10000
2 20000

if you only want customers who live in California, you would put that in a WHERE clause (similar to just wanting 'OS'):

SELECT Customer, SUM(OrderTOtal) From Orders WHERE State = "CA" Group by Customer

If you only want customers who have a OrderTotal sum of greater than 15000 then you would use a HAVING clause:

SELECT Customer, SUM(OrderTotal) FROM Orders GROUP BY Customers HAVING Sum(OrderTOtal) > 15000

To get customers in California with an OrderTotal > 15000 then:

SELECT Customer, SUM(OrderTotal) FROM Orders WHERE State = "CA" GROUP BY Customer HAVING Sum(OrderTotal) > 15000

Hope you have a better understandin of the difference between a WHERE clause and the HAVING clause.[/tt]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi