Sum depending on the value of two fields.

[BasicBoy]

I have a Votes table with a VoteNo and Votename.
I also have a Transactions table with an ID, TransDate, Description, DebitVoteNo, CreditVoteNo and Amount.
The DebitVoteNo and CreditVoteNo corresponds to the VoteNo of the Votes table.

For a specific Vote with VoteNo=SelectVoteNo that I select individually from a comboBox, I need to draw up a grid with the following columns :
Transactions.ID, Transactions.TransDate, Transactions.Description, An amount equal to the Amount field if the VoteNo of the DebitVoteNo is equal to the selected VoteNo, An amount equal to the Amount field if the VoteNo of the CreditVoteNo is equal to the selected VoteNo

It might look like this :

SELECT
Transactions.ID,
Transactions.TransDate,
Transactions.Description,
Amount as DebitAmount where DebitVoteNo=Selected VoteNo,
Amount as CreditAmount from Transactions

WHERE

CreditVoteNo=SelectedVoteNo.

Help is much appreciated

Thanks

 

[dhookom]

Please provide some sample records and desired display in your query.

Duane
Hook'D on Access
MS Access MVP