Improve company productivity with a Business Account.Sign Up

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

Sum Condition

Experts, I am trying to sum tblDraws_Details1.Amount + tblRepayment.Amount but only where tblRepayment.PaymentMadeYN = 'Yes'  (its text).  Meaning that I will add only tblRepayment.Amount to each tblDraws_Details1.Amount but only if tblRepayment.PaymentMadeYN = 'Yes'
YOu can see in the Select statement I do not have any condition for this.  
Please let me know if not clear.

thank you

SELECT
     tblRepayment.ID, tblRepayment.DrawIDrpmt, tblRepayment.PaymentMadeYN, tblDraws_Details1.Amount, tblRepayment.Amount, [tblDraws_Details1.Amount]+[tblRepayment.Amount] AS Balance
FROM
     tblDraws_Details1 INNER JOIN tblRepayment ON tblDraws_Details1.ID = tblRepayment.DrawIDrpmt
ORDER BY
     tblRepayment.DrawIDrpmt;
0
pdvsa
Asked:
pdvsa
  • 2
1 Solution
 
Gustav BrockCIOCommented:
That could be:
SELECT 
     tblRepayment.ID, 
     tblRepayment.DrawIDrpmt, 
     tblRepayment.PaymentMadeYN, 
     tblDraws_Details1.Amount, 
     tblRepayment.Amount, 
     [tblDraws_Details1.Amount]+IIf(tblRepayment.PaymentMadeYN="Yes",[tblRepayment.Amount],0) AS Balance
FROM 
     tblDraws_Details1 
INNER JOIN 
     tblRepayment 
     ON tblDraws_Details1.ID = tblRepayment.DrawIDrpmt
ORDER BY 
     tblRepayment.DrawIDrpmt; 

Open in new window

/gustav
0
 
pdvsaProject financeAuthor Commented:
Nice.  I took a piece from another one you answered the other day and came up with this:
....DSum("Amount","tblRePayment","DrawIDrpmt = " & [DrawIDrpmt] & " and PaymentMadeYN = 'Yes'")

I discovered that I needed a Dsum and i remembered the one from the other day that was very similar.  I actually tried that one from the other day but it wasnit giving me what I needed so I kept modifying and modifying til I asked a question on EE.

thank you.   I hope my questions on this topic are coming to an end.  Have a good night!
0
 
Gustav BrockCIOCommented:
You are welcome - and a very good night for you too!

/gustav
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.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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