Solved

Show Balance

Posted on 2016-10-07
3
39 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
  • 2
3 Comments
 
LVL 50

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 50

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

777 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