 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005Payments Made
I have a table that contains the following field and data for loan payments: ClientID, 56898 FirstPaymentDate, 05/23/04 LastPaymentDate, 10/27/2005 NumberofPaymentsMade, 18 I need a query to select only those clients that have made all their payments, as in the above sample data. Any help is greatly appreciated, Tim Access 2000 > I have a table that contains the following field and data for loan > payments: [quoted text clipped - 11 lines] > Tim > Access 2000 Tim, What is the criteria for deciding whether or not the client has made all their payments? Is 18 the completion payment in all cases? Is a non-Null value in Last Payment Date an indicator that all payments have been made? Please post back with several rows of sample data, and then another list of the rows you will expect to see after the query has executed. Sincerely, Chris O. PS Although meant for an sqlserver newsgroup, the following link is still applicable for MS Access: http://www.aspfaq.com/etiquette.asp?id=5006, and is excellent when it comes to detailing how to provide the information that will best enable others to answer your questions. The FirstPaymentDate is the first payment made on the loan and the LastPaymentDate is the last payment made on the loan and the NumberofPaymentsMade is the number of monthly payments made on a loan. I am trying to detirmine which clients have made all their monthly payments for their loan period. From the same data below, I can figure out this particular client has made 18 payments over a period of 18 months and therefore has not missed a payment. Is it possible to do this with a query with the available fields? ClientID, 56898 FirstPaymentDate, 05/23/2004 LastPaymentDate, 10/27/2005 NumberofPaymentsMade, 18 Thanks, Tim > > I have a table that contains the following field and data for loan > > payments: [quoted text clipped - 37 lines] > provide the information that will best enable > others to answer your questions. > The FirstPaymentDate is the first payment made on the loan and the > LastPaymentDate is the last payment made on the loan and the [quoted text clipped - 13 lines] > > Tim How about: Iif(DateDiff("m", FirstPaymentDate, LastPaymentDate) = NumberOfPaymentsMade, "Made them all", "Didn't Make them All")  SignatureJoe Cilinceon > The FirstPaymentDate is the first payment made on the loan and the > LastPaymentDate is the last payment made on the loan and the [quoted text clipped - 13 lines] > > Tim Tim, I am definitely missing something here. I can see that there are 18 months between the dates given. However, I cannot see how you make the leap to "month between given dates" = "payment made" based on the data given, unless I make some assumptions. Assumption 1: Every month between the dates given indicates a payment having been made. Necessary Corollary: If the Last Payment Date has been made, the number of months between the two payment dates is equal to the number of payments made. By stating that the above sample data is all that is necessary to know whether all the payments have been made, then you only need to check and see whether the Last Payment Date has been entered or not. If it has been entered, all the payments have been made. If it has not been entered, the all of the payments have not been made. NumberofPaymentsMade becomes a calculated value that need not be stored in a table. Purely for my own curiosity, why bother with checking the number of payments made? Once the balance of the loan (including interest) has reached $0.00, that is the clearest indicator that all payments have been made that I can think of. Is this somehow related to monthly checking for past-due on a payment before the loan is completed? No, wait, the Last Payment Date has been entered, so that is over and the loan is done, except that you are checking to see if all payments have been made or not. How can the Last Payment Date be entered into the system when all payments have not yet been made? Won't monthly processing have flagged the loan as "in arrears"? Sincerely, Chris O. PS Although meant for an sqlserver newsgroup, the following link is still applicable for MS Access: http://www.aspfaq.com/etiquette.asp?id=5006, and is excellent when it comes to detailing how to provide the information that will best enable others to answer your questions. The difference between the FirstPaymentDate and the LastPaymentDate gives me the number of months that have passed since the contraction of the loan. This should match the number of payments the client has made on the loan, if not they have either missed a payment or made addition payments. I am downloading many other fields that contain the account balance, principle, interest, outstanding, and other information. The text file I get is generated at a giving time and reflects the account balance and number of payments received at that time. I really want to find the clients that are in good standing and still own their loans. Thanks, Tim > > The FirstPaymentDate is the first payment made on the loan and the > > LastPaymentDate is the last payment made on the loan and the [quoted text clipped - 68 lines] > provide the information that will best enable > others to answer your questions. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.