query help with sum()

  • Thread starter Thread starter Eric Nelson
  • Start date Start date
E

Eric Nelson

Please help this poor novice querier...

Tables of interest are
tFam (famID, name, etc...)
tJournal(jID, famID, ..., charge, credit)

How do I write a query to return families with nonzero
balance, and that balance?

select name, (sum(charge)-sum(credit) as balance)
from (tFram join tJournal on
tFam.famID=tJournal.famID)
where balance <> 0

The puzzling bit is how to get the sums to work.
Thanks in advance for help.
 
I did not test this but try ---

select name, sum([charge]-[credit]) as balance
from (tFram join tJournal on
tFam.famID=tJournal.famID)
where sum([charge]-[credit]) <> 0

There will still be a problem if some of the fields you
are summing are null as a null is not the same as zero.

Then you would need more in the query - replace ([charge]-
[credit]) with
(if([charge]is null,0,[charge])-if([credit] is null,0,
[credit])
 
Hi,


Change the WHERE for HAVING. That should be trivial, if you try to do it
from the query designer... the error message is quite explicit. Since the
WHERE occurs before the SUM which itself occurs before the HAVING, it is
evident that you have to wait the sum to be done before being able to use
it, and so, it is impossible to use it in the WHERE clause.



Hoping it may help,
Vanderghast, Access MVP



karl dewey said:
I did not test this but try ---

select name, sum([charge]-[credit]) as balance
from (tFram join tJournal on
tFam.famID=tJournal.famID)
where sum([charge]-[credit]) <> 0

There will still be a problem if some of the fields you
are summing are null as a null is not the same as zero.

Then you would need more in the query - replace ([charge]-
[credit]) with
(if([charge]is null,0,[charge])-if([credit] is null,0,
[credit])
-----Original Message-----
Please help this poor novice querier...

Tables of interest are
tFam (famID, name, etc...)
tJournal(jID, famID, ..., charge, credit)

How do I write a query to return families with nonzero
balance, and that balance?

select name, (sum(charge)-sum(credit) as balance)
from (tFram join tJournal on
tFam.famID=tJournal.famID)
where balance <> 0

The puzzling bit is how to get the sums to work.
Thanks in advance for help.
.
Click to expand...
Click to expand...
 
select name, sum(...) from...

"CLUNK"
==> You tried to execute a query that does not include
the specified expression 'name' as part of an aggregate
function.
 
SELECT tFam.name,
sum([tFamilyJournal.charge]-[tFamilyJournal.credit])
from (tFam left join tFamilyJournal
on tFam.ID=tFamilyJournal.fid)

still gives the nasty error:
You tried to execute a query that does not include
the specified expression 'name' as part of an aggregate
function

Obviously, I don't "get" what's happening here.
 
SELECT tFam.FamID, Sum(Charge) - Sum(Credit) as Balance
FROM tFam INNER JOIN tJournal
ON tFam.FamId = tJournal.FamID
GROUP BY tFam.FamID
HAVING Sum(Charge) - Sum(Credit) > 0
 
Thanks John, it works.
But WHY do I get the error when I substitute tFam.name
instead of the famID?
Eric
 
Eric, the reason you get that error is because FamID is the join between
your two tables. It is the basis for your query where as tFam.name is not
any kind of key either primary or foreign. HTH Rich
 
HI,



True, you have to add a


GROUP BY name


Hoping it may help,
Vanderghast, Access MVP
 
HI,


In a GROUP BY ( or Total ) query, each expression in the SELECT
should

either be aggregated (SUM, COUNT, MIN, MAX,...)
either be in the Group By clause
either be an arithmetic combination of the previous two cases

Actually, name is in neither in these cases. Adding a GROUP BY clause, and
putting name in it, should do it:


SELECT name, Sum(Charge) - Sum(Credit) as Balance
FROM tFam INNER JOIN tJournal
ON tFam.FamId = tJournal.FamID
GROUP BY name
HAVING Sum(Charge) - Sum(Credit) > 0



Vanderghast, Access MVP
 
Back
Top