Create a sum from a join - easier one 1

[BasicBoy]

I have this one also that I cannot conquer.

I have two tables - one called Votes with fields VoteNumber and VoteName - and one called Transactions with the following fields : ID, Transdate, Description, DebitVoteNumber, CreditVoteNumber, Transtype (1 for Income and 2 for Expenditure) and Amount.

I need to total the Income and Expenditure for every Vote separately like in :

SELECT
Votes.VoteName,
Sum(Income) as TotalIncome (where transtype=1),
sum(Expenditure) as TotalExpenditure (where TransType=2)
FROM
Votes
INNER JOIN
Transactions
ON
Votes.VoteNumber=Transactions.DebitVoteNumber

and again

INNER JOIN
Transactions on Votes.VoteNumber=Transactions.CreditVoteNumber WHERE
date<=MyDate (a parameter) groupby ??? etc.... ran out of ideas.

Help will be very much appreciated.
Thanks

 

[PWise]

DebitVoteNumber, CreditVoteNumber

what are thies two fields

 

[BasicBoy]

They refer to the VoteNumber in the Votes table

 

[PWise]

are all DebitVoteNumber of the same Transtype
and all CreditVoteNumber of the same Transtype

ie
all DebitVoteNumber trans type = 1
all CreditVoteNumber transtype =2

 

[BasicBoy]

No - They can be of any type - 1 to 10, but we need to get only those which are of types 1 and 2 - if the DebitVoteNumber is of type 1 it must add up for Income and if it is type 2 it must add up for Expenditure - and the same for CreditVoteNumber

 

[PWise]

How About

SELECT Votes.VoteName, 
Sum(iif(transtype=1,Income,0)) as TotalIncome ,
sum(iif(transtype=2,Expenditure,0)) as TotalExpenditure 
FROM Votes 
INNER JOIN Transactions 
ON ((Votes.VoteNumber=Transactions.DebitVoteNumber)
or
(Votes.VoteNumber=Transactions.CreditVoteNumber ) )
Group by Votes.VoteName

 

[BasicBoy]

Thank you very much for your help - I just had to put in transactions.amount in the iif part. Can you perhaps help me with the other question on this forum.