Results 1 to 6 of 6
  1. #1
    rmoore is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Feb 2020
    Posts
    47

    Setting up a money loaning DB

    New to designing a DB from scratch, please be patient with me....
    Currently I have 3 tables; Accounts, Customers, Payments.



    My plan is to add forms for data entry after all the tables and queries are finished. Is that a good Practice?

    What is the best way to design it to accomplish the following end "Results" needed

    1. I need all payment dates per account... To give as a payment schedule
    2. I need all remaining payments dates per account... For tracking purposes
    3. Get all payment dates with customer address for the next week... For scheduling collectors
    4. Get the total of expected payments by date range

    Having a hard time designing the queries to get the required results

    Thanks for any suggestions
    Attached Files Attached Files

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    My plan is to add forms for data entry after all the tables and queries are finished. Is that a good Practice?
    yes
    1. I need all payment dates per account... To give as a payment schedule
    possible solution already provided in one of your other threads

    2. I need all remaining payments dates per account... For tracking purposes
    3. Get all payment dates with customer address for the next week... For scheduling collectors
    4. Get the total of expected payments by date range

    Having a hard time designing the queries to get the required results
    you need to provide details of the fields for each table, some example data to cover all possibilities and the expected outcome for each of the requirements together with the sql of the queries you are using. Otherwise we are just guessing at what the problem might be.

    On the face of it, doesn't look like you have enough tables - would think you would need one for collectors and another for the areas they cover - plus the one I suggested to provide a payment schedule

  3. #3
    rmoore is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Feb 2020
    Posts
    47
    Thanks Ajax for all the help, as for the possible solution you already gave for #1. I tried to calculate the dates using calculating field in a query on the loan table. I cant figure out the correct formula to return all the payment dates based on the number of payments.

    I attached the db to show the sample data, I will attach again in a thread reply. I haven't saved any queries yet because i haven't figured out the formula.

    I will add a collector's table, as i can see in the future it will be needed. As for Area table i planned on using the customer table town field to sort the data.

    Basically this is what im trying to get...

    2) Run a query to generate a report showing:
    AcctNo...NumPay...RemainBal...PaymtDatRemain...Pai dPayDate
    12345....6..............$500...............2/15/20...................1/01/20
    .................................................. ....3/01/20...................1/15/20
    .................................................. ....3/15/20...................2/01/20
    24647....3..............$250...............2/29/20...................1/30/20
    .................................................. ....3/30/20
    Only accounts with a remaining balance.

    3) Query or Report showing:
    From.........To...
    1/1/20...2/6/20
    Date...Name..Addre....Town...PhoNo...PayAmt...ReBa l.....RePay
    1/1/...Me......123 ave..xyz......123........$125........$400.......3
    1/9/...Him....987 st....abc......456........$325.......$790........5
    2/4/....Her....714 ave..rex......396........$50..........$50......... .0

    4) Query or Report showing:
    From........To...
    1/01/20...1/30/20
    Date.....AcctNo....PayAm
    1/3.......12345.....$200
    1/14.....49876.....$120
    1/15.....75474.....$75
    NumOfCollec....Total
    3........................$395

    Hopefully this better explains what im trying to do. Is this possible just using calculated fields and sorting, or will this require vbs code?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    no db attached? unless you mean the one in your first post? That has a number of issues, in the accounts table you include the customer name, but you have the ID, so name not required. It also does not have a start date for the loan - presumably the first repayment would be 1 week/whatever after the start of the loan?

    With regards frequency, I suggest you use a value in days, i.e. 1 week =7 days , bi-weekly=14 days? rather than a description. but that give a problem with monthly/quarterly etc since months vary in length. But perhaps you don't use monthly?

    You really need to think through all the options - what if someone pays early? misses a payment? makes a part payment? What about interest? How do you handle the situation where the loan amount does not divide exactly by the number of payments and you are left with a cent or two under or over? How are you matching payment receipts to the schedule? etc.

    Done right, you should not need any vba code and it can all be done using sql - but you need to be 100% clear on all the business rules that need to be applied. The vba loop I provided in the other thread can be replaced with a sql method using another table - see post #7 of this thread - https://www.accessforums.net/showthread.php?t=79631. Different requirement, but the solution would be pretty much the same for your payment schedule

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Something you can start work with.

    In case you use only weekly/bi-weekly frequencies, you can calculate customer payment dates as StartPaymentDate + 7*CountOfCustomerEarlierPayments Or StartPaymentDate + 14*CountOfCustomerEarlierPayments (Use DCount() to calculate CountOfCustomerEarlierPayments). You can also use a tCalendary table I added as example, but I think you don't need it.

    In my example, both given out loan sums (payment type -1) and customer back-payments (payment type 1) are registered in payments table. Account balance may be calculated as sum of PaymentAmount*PaymentType.
    Attached Files Attached Files

  6. #6
    rmoore is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Feb 2020
    Posts
    47
    Thanks alot ArviLaanemets, this is a great start....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. transfer money from by Customers
    By Mehvan in forum Access
    Replies: 9
    Last Post: 01-22-2017, 04:26 AM
  2. Help a newbie keep track of lunch money
    By ericturnerpd in forum Access
    Replies: 15
    Last Post: 02-25-2014, 01:27 AM
  3. Replies: 5
    Last Post: 03-05-2013, 03:30 PM
  4. Access to track money?
    By plasteredric in forum Access
    Replies: 2
    Last Post: 01-20-2013, 07:11 PM
  5. Calculating Old English Money
    By gbjc105 in forum Queries
    Replies: 4
    Last Post: 01-23-2010, 06:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums