help with query

[oaishm]

I just don't understand access sql, it is much different from mssql or mysql. This should be straight forward, but it doesn't work:

SELECT map.code, Sum([Debit Amount]-[Credit Amount])
FROM map left join data on map.code=data.code
GROUP BY map.code
HAVING map.code="600.03" and month([Trx Date])=5 and year([Trx Date])=2009;

Trx Date is Date/Time. The error I get is:
You tried to execute a query that does not include the specified expression map.code="600.03" and month([Trx Date])=5 and year([Trx Date])=2009 as part of an aggregate function. (Error 3122)

This is much harder than other sql

 

[SydneyGeek]

Yeah, Access gets picky with the Group By clause. You need to group on every field that gets a mention in SELECT or HAVING, unless it's got another summary function applied already. Try:

SELECT map.code, Sum([Debit Amount]-[Credit Amount])
FROM map left join data on map.code=data.code
GROUP BY map.code, Month([Trx Date]), Year([Trx Date])
HAVING map.code="600.03" and month([Trx Date])=5 and year([Trx Date])=2009;

Also, use the query builder. It's quick to put together a Totals query that way, and you can switch to SQL view and grab the resulting SQL output.

Denis

 

[james_lankford]

I may be wrong but I don't think you can do it that way either;
I thought that anything you're grouping by has to be in the select statement, and [Trx Date] is not in the select statement

I think what you want to do is change the having to a where

having works on groups: so give me only groups having certain criteria;
where works on individual records: so give me only individual records where a certain criteria is met;

where determines which records go into a group; having determines which groups you get back;

so the original query won't work because it says, give me only groups having a [Trx Date] of 2009;
but there is no group having a [Trx Date]; the only thing any group has is a map.code and a sum; so those are the only things you HAVE to filter on

and I don't think Sydney's solution will work either because even though he is separating things into proper groups, I think anything grouped by has to appear in the select statement (although I may be mistaken about that, so Sydney's may indeed work)

but since where determines which records go into a group and having determines which groups you get back, I think you want to use a where clause and you should probably write your code like this

SELECT 
    map.code, 
    Sum( [Debit Amount] - [Credit Amount] )
FROM 
    map 
left join 
    data 
on 
    map.code = data.code
where
(
    (
        map.code = "600.03" 
        and
        month( [Trx Date] ) = 5 
        and 
        year( [Trx Date] ) = 2009
    )
)
GROUP BY 
    map.code