[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 83
  • Last Modified:

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
0
pdvsa
Asked:
pdvsa
  • 2
1 Solution
 
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
 
Ryan ChongCommented:
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now