Query 2 Return Negative/Positive Values

[Faraz A. Qureshi]

I am quite new to access and have yet designed the following query:

SELECT DISTINCTROW Accounts.[Account No], Accounts.Account,
Sum(Transactions.Amount) AS Balance
FROM Transactions INNER JOIN Accounts ON
Transactions.Debit=Accounts.[Account No] Or
Transactions.Credit=Accounts.[Account No]
GROUP BY Accounts.[Account No], Accounts.Account;

However, I want the Sum(Transactions.Amount) to be shown in positive if
Transactions.Debit=Accounts.[Account No] otherwise a Zero

or in negative if Transactions.Credit=Accounts.[Account No] otherwise a Zero
in a separate column

How to achieve the same?

Thanx in advance.

 

[Daryl S]

Faraz - try this code. It adds two columns to the output query. Each one
tests the account number against the transactions.debit or
transactions.credit, and only counts the value if it matches for the column:

SELECT DISTINCTROW Accounts.[Account No], Accounts.Account,
Sum(Transactions.Amount) AS Balance,
Sum(iif(Transactions.Debit=Accounts.[Account No],[Transactions]![Amount],0))
As Debit, Sum(iif(Transactions.Credit=Accounts.[Account
No],[Transactions]![Amount],0)) As Credit
FROM Transactions INNER JOIN Accounts ON
Transactions.Debit=Accounts.[Account No] Or
Transactions.Credit=Accounts.[Account No]
GROUP BY Accounts.[Account No], Accounts.Account;

 

[Faraz A. Qureshi]

XClent approach Daryl!
Outstanding 4 sure!
However, some of the accounts are showing amounts in BOTH the columns!
Any idea how 2 have the net amount be remaining in the column which has the
largest value?
Thanx again!
--
Best Regards,

Faraz

Faraz - try this code. It adds two columns to the output query. Each one
tests the account number against the transactions.debit or
transactions.credit, and only counts the value if it matches for the column:

SELECT DISTINCTROW Accounts.[Account No], Accounts.Account,
Sum(Transactions.Amount) AS Balance,
Sum(iif(Transactions.Debit=Accounts.[Account No],[Transactions]![Amount],0))
As Debit, Sum(iif(Transactions.Credit=Accounts.[Account
No],[Transactions]![Amount],0)) As Credit
FROM Transactions INNER JOIN Accounts ON
Transactions.Debit=Accounts.[Account No] Or
Transactions.Credit=Accounts.[Account No]
GROUP BY Accounts.[Account No], Accounts.Account;


--
Daryl S

I am quite new to access and have yet designed the following query:

SELECT DISTINCTROW Accounts.[Account No], Accounts.Account,
Sum(Transactions.Amount) AS Balance
FROM Transactions INNER JOIN Accounts ON
Transactions.Debit=Accounts.[Account No] Or
Transactions.Credit=Accounts.[Account No]
GROUP BY Accounts.[Account No], Accounts.Account;

However, I want the Sum(Transactions.Amount) to be shown in positive if
Transactions.Debit=Accounts.[Account No] otherwise a Zero

or in negative if Transactions.Credit=Accounts.[Account No] otherwise a Zero
in a separate column

How to achieve the same?

Thanx in advance.