Show Balance

Experts,

I have the following query:
SELECT 
    tblDraws_Details1.ID, tblRepayment.DrawIDrpmt, tblDraws_Details1.Amount, tblRepayment.Amount, tblRepayment.PaymentMadeYN, DSum("Amount","tblDraws_Details1","ID = " & [DrawIDrpmt] & " and PaymentMadeYN = '" & [PaymentMadeYN] & "'")+DSum("Amount","tblRePayment","DrawIDrpmt = " & [DrawIDrpmt] & " and PaymentMadeYN = 'Yes'") AS balance
FROM
    tblDraws_Details1 INNER JOIN tblRepayment ON tblDraws_Details1.ID = tblRepayment.DrawIDrpmt;

Open in new window


The balance doesnt show anything though.  Its NULL.

What I am wanting to do is display a balance according to if the [PaymentMadeYN] (its a text)  is 'Yes' or 'No'.  If yes then tblDraws_details.amount + tblRepayment.amount (repayment amount is a negative number) but if 'No' then do not add that repayment amount.

I have this query in a form and it works but I am trying to make a separate query i/o having it in a form.

I have attached a db with 2 tables and they query.  
thank you for your help.  Greatly appreciated.
EE_bal.accdb
pdvsaProject financeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ryan ChongConnect With a Mentor Commented:
after re-read your requirements, I think you should have this instead:
SELECT tblDraws_Details1.ID, tblRepayment.DrawIDrpmt, tblDraws_Details1.Amount, tblRepayment.Amount, tblRepayment.PaymentMadeYN, 

DSum("Amount","tblDraws_Details1","ID = " & [DrawIDrpmt] ) as Total,
NZ(DSum("abs(Amount)","tblRePayment","DrawIDrpmt = " & [DrawIDrpmt] & " and PaymentMadeYN = 'Yes' " ),0) as Repayment,
(DSum("Amount","tblDraws_Details1","ID = " & [DrawIDrpmt] ) - NZ(DSum("abs(Amount)","tblRePayment","DrawIDrpmt = " & [DrawIDrpmt] & " and PaymentMadeYN = 'Yes' " ), 0) ) * -1 as OutstandingBalance,

DSum("Amount","tblDraws_Details1","ID = " & [DrawIDrpmt] & " and PaymentMadeYN = '" & [PaymentMadeYN] & "'")+DSum("Amount","tblRePayment","DrawIDrpmt = " & [DrawIDrpmt] & " and PaymentMadeYN = 'Yes'") AS bal2
FROM tblDraws_Details1 INNER JOIN tblRepayment ON tblDraws_Details1.ID = tblRepayment.DrawIDrpmt
order by  tblDraws_Details1.ID

Open in new window

0
 
Ryan ChongCommented:
Do you mean to show the outstanding amount for the transactions based on "amount" and "id" ?

if yes, then you can try:
SELECT tblDraws_Details1.ID, tblRepayment.DrawIDrpmt, tblDraws_Details1.Amount, tblRepayment.Amount, tblRepayment.PaymentMadeYN, 

DSum("Amount","tblDraws_Details1","ID = " & [DrawIDrpmt] ) as Total,
DSum("abs(Amount)","tblRePayment","DrawIDrpmt = " & [DrawIDrpmt] ) as Repayment,
(DSum("Amount","tblDraws_Details1","ID = " & [DrawIDrpmt] ) - DSum("abs(Amount)","tblRePayment","DrawIDrpmt = " & [DrawIDrpmt] )) * -1 as OutstandingBalance,

DSum("Amount","tblDraws_Details1","ID = " & [DrawIDrpmt] & " and PaymentMadeYN = '" & [PaymentMadeYN] & "'")+DSum("Amount","tblRePayment","DrawIDrpmt = " & [DrawIDrpmt] & " and PaymentMadeYN = 'Yes'") AS bal2
FROM tblDraws_Details1 INNER JOIN tblRepayment ON tblDraws_Details1.ID = tblRepayment.DrawIDrpmt
order by  tblDraws_Details1.ID

Open in new window

I make the outstanding balance in "positive" view.
0
 
pdvsaProject financeAuthor Commented:
Hi Ryan, I think that is it!  I have not tested completely though as I am walking out the door.  Will revert later when return to computer.  thanks again.  it looks good.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.