pdvsa
asked on
Balance after Repayment - 2
Experts,
This is a follow up to:
https://www.experts-exchange.com/questions/29018712/Balance-After-Payment.html?anchor=a42112146¬ificationFollowed=188237686&anchorAnswerId=42112146#a42112146
I will try to make this simple.
The below query works however I need to modify the calculation for whether or not a [PaymentMadeYN] = "Yes". If "No" then the [Remaining Balance] is not reduced by the payment amount.
I have bolded the subquery. I think this is what would need to be modified. Note that the [PaymentMadeYN] is not a true yes no field...its a row source with "Yes";"No". Possibly a report would be a better approach but I would need to export the data to excel and it might not export well from a report and this is why I need it to be at the query level.
SELECT tblDraws_Details1.ValueDat e, tblDraws_Details1.Amount, tblRepayment.Amount, tblRepayment.PaymentMadeYN , tblRepayment.ValueDate, tblRepayment.DrawIDrpmt, tblRepayment.ID, (Select sum(P.Amount) from tblRepayment as P where tblRepayment.DrawIDrpmt=P. DrawIDrpmt and P.ID<= tblRepayment.ID) AS Payment, [tblDraws_Details1].[Amoun t]+[Paymen t] AS [Remaining Balance]
FROM tblDraws_Details1 INNER JOIN tblRepayment ON tblDraws_Details1.ID = tblRepayment.DrawIDrpmt
GROUP BY tblDraws_Details1.ValueDat e, tblDraws_Details1.Amount, tblRepayment.Amount, tblRepayment.PaymentMadeYN , tblRepayment.ValueDate, tblRepayment.DrawIDrpmt, tblRepayment.ID
ORDER BY tblDraws_Details1.ValueDat e, tblRepayment.ID;
EE_Balance_4-26-17.accdb
This is a follow up to:
https://www.experts-exchange.com/questions/29018712/Balance-After-Payment.html?anchor=a42112146¬ificationFollowed=188237686&anchorAnswerId=42112146#a42112146
I will try to make this simple.
The below query works however I need to modify the calculation for whether or not a [PaymentMadeYN] = "Yes". If "No" then the [Remaining Balance] is not reduced by the payment amount.
I have bolded the subquery. I think this is what would need to be modified. Note that the [PaymentMadeYN] is not a true yes no field...its a row source with "Yes";"No". Possibly a report would be a better approach but I would need to export the data to excel and it might not export well from a report and this is why I need it to be at the query level.
SELECT tblDraws_Details1.ValueDat
FROM tblDraws_Details1 INNER JOIN tblRepayment ON tblDraws_Details1.ID = tblRepayment.DrawIDrpmt
GROUP BY tblDraws_Details1.ValueDat
ORDER BY tblDraws_Details1.ValueDat
EE_Balance_4-26-17.accdb
ASKER
Rey,
It might need to be tweaked.
If there are >1 repayments and one is Yes and the other is No then the remaining balance shown could be a little misleading.
For example, the record DrawIDrpmt = 104 has >1 repayment and if the first record is Yes then the balance is 543,750,000.00, which is correct, but the remaining balance (for the record below it) still shows 1,087,500,000.00 but it would be better to show 543,750,000.00. I hope this makes sense.
this is the SQL from the revised db in your answer above:
SELECT tblDraws_Details1.ValueDat e, tblDraws_Details1.Amount, tblRepayment.Amount, tblRepayment.PaymentMadeYN , tblRepayment.ValueDate, tblRepayment.DrawIDrpmt, tblRepayment.ID, (Select sum(P.Amount) from tblRepayment as P where tblRepayment.DrawIDrpmt=P. DrawIDrpmt and P.ID<= tblRepayment.ID) AS Payment, IIf(tblRepayment.PaymentMa deYN="Yes" ,tblDraws_Details1.Amount+ [Payment], tblDraws_D etails1.Am ount) AS [Remaining Balance]
FROM tblDraws_Details1 INNER JOIN tblRepayment ON tblDraws_Details1.ID = tblRepayment.DrawIDrpmt
GROUP BY tblDraws_Details1.ValueDat e, tblDraws_Details1.Amount, tblRepayment.Amount, tblRepayment.PaymentMadeYN , tblRepayment.ValueDate, tblRepayment.DrawIDrpmt, tblRepayment.ID
ORDER BY tblDraws_Details1.ValueDat e, tblRepayment.ID;
It might need to be tweaked.
If there are >1 repayments and one is Yes and the other is No then the remaining balance shown could be a little misleading.
For example, the record DrawIDrpmt = 104 has >1 repayment and if the first record is Yes then the balance is 543,750,000.00, which is correct, but the remaining balance (for the record below it) still shows 1,087,500,000.00 but it would be better to show 543,750,000.00. I hope this makes sense.
this is the SQL from the revised db in your answer above:
SELECT tblDraws_Details1.ValueDat
FROM tblDraws_Details1 INNER JOIN tblRepayment ON tblDraws_Details1.ID = tblRepayment.DrawIDrpmt
GROUP BY tblDraws_Details1.ValueDat
ORDER BY tblDraws_Details1.ValueDat
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Gustav, that's amazing. I have been studying it for 30 min and well I don't really get that P part. I posted the same question on a Linked In VBA group and Utteraccess and no responses. Makes me think the heavy hitters are here on EE. Thank you once again. Yes, the amounts are big....Saudi oil is big biz :)...have a good day.
my bad, sorry got busy and forgot about this thread
Tony, the P is an alias for tblRepayment:
from tblRepayment as P
This means that the query looks at two "instances" of the same table, where P is the "inner instance" (that wrapped in Nz) while tblRepayment is the "outer instance". This way you can for each record of tblRepayment look up aggregated values from the same table by using P.
NZ only serves to return 0 (zero) where there is not an aggregated value.
UtterAccess I left many years ago. It's a strange forum lead by some "gods" - too much for me.
LinkedIn I don't join, but I've heard from others that it is very well for job seeking but that's it.
The only other forum I find of value regarding Access is Stack Overflow, but that is coding only.
Hope you enjoy the heat and sand. I can tell you that here we've had the coldest spring in decades and it isn't expected to change in the upcoming weeks. I'm tired of wearing coats.
/gustav
from tblRepayment as P
This means that the query looks at two "instances" of the same table, where P is the "inner instance" (that wrapped in Nz) while tblRepayment is the "outer instance". This way you can for each record of tblRepayment look up aggregated values from the same table by using P.
NZ only serves to return 0 (zero) where there is not an aggregated value.
UtterAccess I left many years ago. It's a strange forum lead by some "gods" - too much for me.
LinkedIn I don't join, but I've heard from others that it is very well for job seeking but that's it.
The only other forum I find of value regarding Access is Stack Overflow, but that is coding only.
Hope you enjoy the heat and sand. I can tell you that here we've had the coldest spring in decades and it isn't expected to change in the upcoming weeks. I'm tired of wearing coats.
/gustav
EE_Balance_4-26-17_Rev.accdb