how to achieve totals based on grouping/creterias 2

[jamaarneen]

Hi
I have tried some options (and stopped when thinks didn't went smooth & simple...), I have also read some stuff - but I'm still not sure what is the right approch?

What did I have?
A Query (that is based on 3 tables: tblProperties, tblPartners, tblTransactions), that provides a list of transactions with data like: date, PartnerName, PropertyCode, Amount, TransactionDirection (=if it was a payment or a income).

What results do I want?
I want to get the sum (not count) of all transaction, grouped per property, and, grouped by transaction direction (well, later I would also might try to group per partner, or period...).

In other words, it should give results simular like this:
Property Total payments Total income
prop code 1 sum payments sum incomes


[blue]The question is[/blue], how and what is the best & most simple way do achieve the above result?
- create a query that should give the results (and then bound a form on that query).
[If so, should i use a special query (maybe UNION), or a simple select, but with some expression as fields? (I tried working with DSum, but with no luck)]
or
- it’s not important to build a quety for that. Just to build a form on the original transaction query, and create on the form a few controls (textboxes) and build the control source should give the desired results (here too, I tried DSum - no luck :-( ).

please point me to the right direction... Thanks
Ja

 

[PHV]

SQL code:
SELECT yourPropertyField
, Sum(IIf(yourDirectionField='payment',yourAmountField,0)) AS [Total payments]
, Sum(IIf(yourDirectionField='income',yourAmountField,0)) AS [Total income]
FROM yourTableOrQuery
GROUP BY yourPropertyField

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jamaarneen]

OK, I'm back now, and: Thanks PHV, it's just great!
I tried it and it worked.

[And I discovered something - strange to me:
If I want some more information about the property (let's say, I have: ID, code, and Name), and in the GROUP BY clause, I have only propertyID,
then I'll get an (error) message telling something about not having Code or Name in any expression. but when I added the tow fields also in the GROUP BY, then it's OK. I don't really understand, why should SQL not be able to group only by propertyID?
OOOOOPS!!! [blue]well, I think that rightnow I have got the answer: SQL wouldn't know what to do, if one propertyID will have several Code or Name (what is possible). so that's why you to choose: or take all of them in the GROUP BY, all get it out of the query...[/blue]]

so I spare you an answer...
but please just tell me: is my assumption right?

Thanks
Ja

BTW: does anyone knows, is here a forum to ask/discus slike "Tek-Tip_issues"?

 

[lespaul]

yes you understand it correctly...every non-aggregate field in the SELECT portion of the statement must be in the GROUP BY portion.

Leslie

Have you met Hardy Heron?

 

[jamaarneen]

Thank you Leslie, for your brief & comprehensive definition!
therefor: a star

Ja