Results 1 to 10 of 10
  1. #1
    ahill48 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    18

    Create an account balance in subdatasheet

    Hi All,

    I'm new to Access, and I've been tasked with creating a simple database to track checks written by customers. One of my main considerations is that the individuals who will be entering data are not very computer literate, so the input must be very simple and straightforward.

    When a customer writes a bad check they owe the check amount and fees. I have a main table for customers containing their basic information, and a subdatasheet to enter each check written as a new record. The checks subdatasheet has fields for Check amount, Check fees, (calculated) Total, Checks paid, Fees paid, and (calculated) Balance due. When a check is written, the Check amount is entered, with zeros in the Fees, Checks paid, and Fees paid fields, which should show a balance due of zero. However, it currently shows the amount of the check.

    If a check is returned NSF by the bank, the fee amount is entered, which should then generate the balance due. When a payment is made I would like the information entered on as a new record with zeros for check amount and fees, but $ amounts in the checks paid and the fees paid fields. As I have it set up at the moment, the balance due is calculated for each check, and payments have to be entered in the same record as the checks are on to update that balance. Some customers have multiple checks, and many make partial payments on their NSF checks. The way I have it set up now additional payments would have to be added up manually and the payment amounts reentered. I would like there to be no balance due unless there are fees, and have it to keep a running total, so that each time an NSF check is written, or paid the total balance due for all NSF checks is automatically updated.

    I apologize if my question isn't phrased succinctly, but I've never built a database before and I have learned how to do everything so far by searching the web...I just can't seem to find any information on this. I am really hoping that I won't have to write any code, as I think that might be beyond me. I've probably not set it up right in the first place, but I know it's possible, I just need help to figure out how to do it. Thank you in advance for any help you can give me.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Few qustions to get started:

    1. Can you list out each table and fields and give examples of data in each? Is it:
    tblCustomer:
    CustomerID
    CustomerName

    tblChecks:
    ChecksID
    CheckNumber
    CheckAmount
    CheckFees
    CheckTotal
    CheckPaid
    FeePaid
    Balance Due

    2. Each customer in that table is a unique record?
    3. A customer can write multiple checks to pay off a balance?
    4. Is the output what you are viewing all in a form or you enter data in form and it has reports attached to give you the output?

  3. #3
    ahill48 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    18
    Hi Bulzie, Thanks for helping.
    Here is the list of tables and their fields. The ones that have (drop down list) are supporting tables for the two main tables.

    *_tblCustomers:_*
    CustomerNumber (key)
    LastName
    FirstName
    CustomerStatus
    Address1
    Address2
    City
    State
    Zip
    Phone
    DriversLicense
    Casino
    Notes

    *_tblChecks:_* (Subdatasheet)(no Key)
    LastName
    FirstName
    Address1
    CheckNumber
    TakenBy
    Casino
    CheckDate
    CheckAmount
    Fees
    CheckTotal
    ChecksPaid
    FeesPaid
    BalanceDue
    Comments

    *_tblCasinos:_* (drop down list)
    ID
    Casino

    *_tblTakenBy:_* (drop down list)
    ID
    ServerName

    *_tblStatus:_* (drop down list)
    ID
    Status

    Each customer in that table is a unique record? Yes each customer is a unique record

    A customer can write multiple checks to pay off a balance? A customer can write multiple checks, if any check is returned by the bank they then owe a balance. A customer may make multiple payments to resolve the balance.

    Is the output what you are viewing all in a form or you enter data in form and it has reports attached to give you the output? The main user interface is a form for Searching/Entering Customers and or Checks. There are some reports for management, but I'm still working those out, however the main way it will be used/viewed is in the casinos to search by customer name or phone number to find out the customers check cashing status, (Unlimited, Good Standing, or NO CHECKS)

    Quote Originally Posted by Bulzie View Post
    Few qustions to get started:

    1. Can you list out each table and fields and give examples of data in each? Is it:
    tblCustomer:
    CustomerID
    CustomerName

    tblChecks:
    ChecksID
    CheckNumber
    CheckAmount
    CheckFees
    CheckTotal
    CheckPaid
    FeePaid
    Balance Due

    2. Each customer in that table is a unique record?
    3. A customer can write multiple checks to pay off a balance?
    4. Is the output what you are viewing all in a form or you enter data in form and it has reports attached to give you the output?
    Last edited by ahill48; 08-23-2016 at 07:27 AM. Reason: Incomplete response

  4. #4
    ahill48 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    18
    I forgot to answer your questions:

    Each customer in that table is a unique record? Yes each customer is a unique record

    A customer can write multiple checks to pay off a balance? A customer can write multiple checks, if any check is returned by the bank they then owe a balance. A customer may make multiple payments to resolve the balance.

    Is the output what you are viewing all in a form or you enter data in form and it has reports attached to give you the output? The main user interface is a form for Searching/Entering Customers and or Checks.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    More questions:
    1. So they write you a check for something. If it clears all is good. If NSF, on that check record, you mark they now have fees and a balance and you wait for another check from them to pay it off?
    2. Then when they send you another check, you add a new record for that 2nd check and on this one fees are 0 and balance is 0 and you leave the first check record as is? So you now have 2 check records?
    3. Can they pay by Credit Card or Cash or only another check?
    4. Can they pay it off in installments so could be multiple checks?
    5. Is whatever fee they are paying a recurring thing meaning they gave you a check in January and will give you another in Feb, etc.?
    6. From your info, if they have NSF and send you another check, you want to record another record with the new check info with fees and balance info on that record? So you would have 2 check records

    Few things to start

    1. I would replace LastName, FirstName, Address in tblChecks with the CustomerNumber as you can always link back to tblCustomer to get those values. Unless those fields on the check could be different from the Customer(as in someone else is paying), then keep them but still add CustomerID as that will be your link between the 2 tables.
    2. I would make CheckNumber the key to tblChecks as that cannot be duplicated right? In the Relationships, it would be a 1 to many Link (tblCustomer to tblChecks) on CustomerNumber.
    3. tblChecks needs the Status field. What are the values in the Status field? Seems like it would be (Paid, NSF, Not Cleared), something like that?

    Looks like you would have a main form for tblCustomer and a subform for the checks(continuous form maybe). Not sure if the balance and fees should be on the tblCustomer record. You could create a 3rd table to link the checks and customers but not sure if that is needed. Others might have more suggestions on best ways. Hard to know until you create some of it.

  6. #6
    ahill48 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    18
    More Questions,

    1. So they write you a check for something. If it clears all is good. If NSF, on that check record, you mark they now have fees and a balance and you wait for another check from them to pay it off? WE accept checks to the Casino for cash, as a courtesy so they can play. If the check is good, no fees are added. If it's NSF and returned by the bank, we assess the fees and proceed to collect on the check by whatever legal means at our disposal. We do not accept repayment in the form of another check. They would be paying us in either cash or money order since we've already been burned once.
    2. Then when they send you another check, you add a new record for that 2nd check and on this one fees are 0 and balance is 0 and you leave the first check record as is? So you now have 2 check records?The payments are recorded as a new record on the Subdatasheet, if cash we'll put the word cash in the check # field, if money order we'll enter MO and any number on that document. Then the $ amount's are entered into the Checks Paid and Fees Paid fields. Fees are paid off first if they are making partial payments.
    3. Can they pay by Credit Card or Cash or only another check? Cash or Money order payments only.
    4. Can they pay it off in installments so could be multiple checks? Many customers write more than one check, we allow up to 3 every 10 days. So if they write a check on the 1st, another on the 3rd, and an additional check on 5th of the month, they will have to wait until the 15th to write another check...assuming none have come back NSF.
    5. Is whatever fee they are paying a recurring thing meaning they gave you a check in January and will give you another in Feb, etc.? The fees are only assessed if the checks are returned by the bank.
    6. From your info, if they have NSF and send you another check, you want to record another record with the new check info with fees and balance info on that record? So you would have 2 check records. I need a way to enter payments separately from the checks. I had thought of using a separate table to record payments, but couldn't figure out how to integrate them and get a balance...I thought it would be easier to put them on the same table but ended up with the same problem of not having a total balance due.

    Few things to start

    1. I would replace LastName, FirstName, Address in tblChecks with the CustomerNumber as you can always link back to tblCustomer to get those values. Unless those fields on the check could be different from the Customer(as in someone else is paying), then keep them but still add CustomerID as that will be your link between the 2 tables. I had thought of that originally, but we have many customers and I wasn't sure how the data entry person would be able to know or find the Customer Number. I wasn't able to have a key field because of duplication issues...I'm actually not sure I understand how the key works. As I mentioned I've never built a database before.
    2. I would make CheckNumber the key to tblChecks as that cannot be duplicated right? In the Relationships, it would be a 1 to many Link (tblCustomer to tblChecks) on CustomerNumber. Check numbers are often duplicated from one customer to another. I tried a few different combinations of fields to create a key, but duplication was an issue with all of them.
    3. tblChecks needs the Status field. What are the values in the Status field? Seems like it would be (Paid, NSF, Not Cleared), something like that? Status on the Customer table is there to indicate whether the customer can cash checks or not. I think the idea of putting it on the Checks table is a good one, though. Is there any way to make the status field on the checks create the fees and balance due if its marked NSF?

    Looks like you would have a main form for tblCustomer and a subform for the checks(continuous form maybe). Not sure if the balance and fees should be on the tblCustomer record. You could create a 3rd table to link the checks and customers but not sure if that is needed. Others might have more suggestions on best ways. Hard to know until you create some of it. As I have it now, there is just one split form with a couple of search fields at the top (text field) (Command Button with a macro to apply filter) The Customer table and Checks table are linked in a direct relationship...I am not sure how to do any other types of connections.

    As you can see I don't know a whole lot...I'm an excel person. I design and maintain spreadsheets and related reports. One of my clients asked me a couple of years ago to find someone who would build this database, but when I found several individuals he wasn't willing to pay want they wanted...so I'm doing it at my regular billing rate. I suspect he's not really going to save himself much as he is also paying for me to learn how! LOL...but I warned him up front. I also required him to buy me a copy of Access.

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So for a general summary, I would try to keep it at 2 tables, Customers and Checks. Link them by CustomerNumber(so add that field to the Checks table). Make a MainForm with tblCustomers as the RecordSource and add those fields to the Form at the top. Create a 2nd Form (Continuous records) for the Checks which will be a SubForm in the MainFrm in the Detail section. Use the CustomerID in the Parent/Child Link on the Subform. This will mean when you select a new Customer it will show their specific checks at the bottom.

    On the Check Subform, I would just put all the fields you need in that table. You could create a 3rd table for NSF Payments but I'm not sure that is a must. I would add these fields:
    NSF (Yes or No) combo box default to No
    NSFPaymentType (Cash, Money Order) combo box leave as default to null
    NSFPaymentAmount

    So idea would be
    1. When check comes in, you enter the check info. NSF is already defaulted to "No", NSFPaymentType = Null, NSFPaymentAmount = 0, Fees = 0, Balance = 0.

    2. If check comes back NSF, change field NSF = "Yes" and in AferUpdate of that NSF combo box, update the Fees and Balance:
    Me.Fees = CheckAmount * .10 or whatever the calculation is for Fees.
    Me.Balance = Me.CheckAmount + Me.Fees

    3. When they give you say cash to pay it off, update NSFPaymentAmount to the amount they gave you, NSFPaymentType = "Cash" and in AfterUpdate on NSFPaymentType:
    Me.Fees = 0 (may want to just leave this at the fee amount and not zero out.
    Me.Balance = Me.Balance - NSFPaymentAmount. If they pay the exact amount Balance is 0, if they pay Partial, Balance is what they have left.

    Others might offer something different, this is just one approach from what I understood from the process.

  8. #8
    ahill48 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    18
    Hi Again,
    So, I took your advice, and added the Customer Number to the Checks table and that made the relationship more direct. I added the field for NSF (Yes/No), to add the fees and a field for Fee discount, because, sometimes our collections will negotiate lower fees or assess higher ones for large NSF checks. This will populate the NSF total while balance due field calculates the NSF total minus fees paid and checks paid. I also added a text box for the total balance, thinking I could use an expression to sum the Balance due column in the subform (checks) data. I have a included an image of the split Enter Customer-Checks form I'm using so you can see what I mean. I was unable to figure out what the expression I needed. I don't have any real experience in expression builder. I'm hoping you can help. I will still have the issue of data entry having to enter payments on the same record as the check, thus the balance due will be for each check.

    Click image for larger version. 

Name:	Searh-Enter Form.jpg 
Views:	19 
Size:	138.9 KB 
ID:	25565



    Quote Originally Posted by Bulzie View Post
    So for a general summary, I would try to keep it at 2 tables, Customers and Checks. Link them by CustomerNumber(so add that field to the Checks table). Make a MainForm with tblCustomers as the RecordSource and add those fields to the Form at the top. Create a 2nd Form (Continuous records) for the Checks which will be a SubForm in the MainFrm in the Detail section. Use the CustomerID in the Parent/Child Link on the Subform. This will mean when you select a new Customer it will show their specific checks at the bottom.

    On the Check Subform, I would just put all the fields you need in that table. You could create a 3rd table for NSF Payments but I'm not sure that is a must. I would add these fields:
    NSF (Yes or No) combo box default to No
    NSFPaymentType (Cash, Money Order) combo box leave as default to null
    NSFPaymentAmount

    So idea would be
    1. When check comes in, you enter the check info. NSF is already defaulted to "No", NSFPaymentType = Null, NSFPaymentAmount = 0, Fees = 0, Balance = 0.

    2. If check comes back NSF, change field NSF = "Yes" and in AferUpdate of that NSF combo box, update the Fees and Balance:
    Me.Fees = CheckAmount * .10 or whatever the calculation is for Fees.
    Me.Balance = Me.CheckAmount + Me.Fees

    3. When they give you say cash to pay it off, update NSFPaymentAmount to the amount they gave you, NSFPaymentType = "Cash" and in AfterUpdate on NSFPaymentType:
    Me.Fees = 0 (may want to just leave this at the fee amount and not zero out.
    Me.Balance = Me.Balance - NSFPaymentAmount. If they pay the exact amount Balance is 0, if they pay Partial, Balance is what they have left.

    Others might offer something different, this is just one approach from what I understood from the process.
    Attached Thumbnails Attached Thumbnails 2016-08-25_8-31-02.jpg  

  9. #9
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe try this:
    Make Total Balance field on Main form unbound. In the AfterUpdate Event of your Checks subform, sum all the totals for all the check records for that customer. You might need to add a hidden field for CustomerID on the Checks subform.

    Forms![MainForm]!TotalBalance = DSum("[Total]", "tblChecks", "[CustomerID] = "& me.CustomerID)

    ================================================== ===
    If you need to save the total in the Customers table, maybe do an update line instead:

    vBalance = DSum("[Total]", "tblChecks", "[CustomerID] = "& me.CustomerID)
    DoCmd.SetWarnings = False
    Docmd.RunSQL "UPDATE tblCustomer SET tblCustomer.TotalBalance = vBalance WHERE (((tblCustomer.CustomerID)= " & me.CustomerID));"
    DoCmd.SetWarnings = True

    If you testing this make sure you have a good backup of those tables.

  10. #10
    ahill48 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    18
    When I click in the checks subform, there isn't an "After Update event in the properties. these are the options on the events tab:
    On Enter
    On Exit

    There is an After Update event for the Total Balance text box.
    This is what I entered into the After Update event for the Total Balance text box: [Forms]![Search - Enter]![Text277] = DSum("[Balance Due]", "[Customer Number] = "& me.Customer Number)
    This returned and invalid syntax error. I so don't know what I'm doing...thank you for your patience.


    Quote Originally Posted by Bulzie View Post
    Maybe try this:
    Make Total Balance field on Main form unbound. In the AfterUpdate Event of your Checks subform, sum all the totals for all the check records for that customer. You might need to add a hidden field for CustomerID on the Checks subform.

    Forms![MainForm]!TotalBalance = DSum("[Total]", "tblChecks", "[CustomerID] = "& me.CustomerID)

    ================================================== ===
    If you need to save the total in the Customers table, maybe do an update line instead:

    vBalance = DSum("[Total]", "tblChecks", "[CustomerID] = "& me.CustomerID)
    DoCmd.SetWarnings = False
    Docmd.RunSQL "UPDATE tblCustomer SET tblCustomer.TotalBalance = vBalance WHERE (((tblCustomer.CustomerID)= " & me.CustomerID));"
    DoCmd.SetWarnings = True

    If you testing this make sure you have a good backup of those tables.

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

Similar Threads

  1. Calculate bank account balance
    By dougdrex in forum Queries
    Replies: 8
    Last Post: 11-28-2014, 01:59 PM
  2. Replies: 4
    Last Post: 10-16-2014, 11:02 AM
  3. Replies: 8
    Last Post: 03-21-2014, 04:31 AM
  4. account balance db help
    By Suzie2012 in forum Database Design
    Replies: 2
    Last Post: 07-15-2012, 09:16 PM
  5. Replies: 0
    Last Post: 02-15-2009, 09:14 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