Solved

Show Balance

Posted on 2016-10-07
3
50 Views
Last Modified: 2016-10-07
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
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41833377
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
 
LVL 51

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41833386
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
 

Author Closing Comment

by:pdvsa
ID: 41833397
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
access vba query parametres from a repport 1 27
Null or "" 28 52
Sharepoint list to Access database 9 47
Populate a MS Access field based on contents of 2 other fields. 5 27
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question