Free Loan Foundation database

[moishy]

Hi folks,

I'm trying to put together a db that can keep track of what happens in our free loan foundation.
There are depositors and borrowers (at times the same person may be both) most loans are given in monthly installments.
I'm not sure of the table structure necessary to allow multiple loans/deposits with multiple due-dates for the same person.
I need to store information for each installment (i.e. due-date, amount etc.).

 

[Pat Hartman]

Rather than asking us to design the application for you - we don't have the specs - why not post what you've got and we'll help refine your ideas.

 

[moishy]

Pat,

Thanks for your willingness to help. I'm not very good with words so instead of trying to describe the structure of the tables I have so far (which most probably is doomed to failure) I'll post the relevant mdb.
I'm aware the many people are hesitant to trust files from unknown sources, I can reassure all that there is no code or macros in this file, additionally it can be opened using the shift key.
I hope this is ok.

Thanks in advance

moishy

 

[cplmckenzie]

moishy,

A couple of initial questions.

1. tblTransations,

if is a depositor Transaction Type = Credit
if is a borrower Transaction Type = Debit against Loan Balance

is the GuarantorID Similar to a CoSigner
if so do you have a table to store CoSigner information.

cplmckenzie

 

[moishy]

cplmckenzie,

Yes, that is what I had in mind for the transaction types, is there a better way to setup that part?

And, yes, the Guarantor is a CoSigner. I thought to use the tblContact to store their details, and transaction type would be Guarantor (CoSigner).

 

[cplmckenzie]

moishy,

I am looking at the table data to be stored.

Firstly, I will rename tblContact to tblGuarantor, so as to avoid
confusion when access table data.

You will need, at the very least.....

tblAccountHolder ... the person that actually has the credit\debit account

tblAccountsDebits .... to store all information concerning that account

tblAccountsCredits ... to store all information concerning that account

tblAccountType ... loan, checking, savings, creditcard, others that a particular AccountHolder may have.

tblTransactionType ... Will permit the tracking of all accounts that one person may have on file.

Each of these tables will hold data that is linked by a key field in each table that will identiyf each transactions for all accounts by a particular person.

Give some thought to what type of information that you would like to have for each of these tables of data.

After I see what you want, I will do a table design layout showing how all this data will be used together, before I begin let me say YOU ONLY NEED TO PROVIDE THE TABLE STRUCTURE .... NOT ANY REAL DATA AS THIS DESIGN WILL BE BUILT AROUND DUMMY INFO .... just to make sure your database will do what you require.

Will wait for next post.

cplmckenzie

 

[moishy]

Is it not better to use one table for all contact information, and have three y/n columns (Guarantor, Borrower, Depositor)?

 

[cplmckenzie]

mosihy,

Yes, there should be one MASTER table that has all persons basic contact info such as name, address, city, state, zip, phone#, etc.

But tblAccountHolder could have more than 1 entry for "John Doe", depending on how many different account types he has.

tblGuarantor will hold other NON-BASIC

 

[cplmckenzie]

mosihy,

Yes, there should be one MASTER table that has all persons basic contact info such as name, address, city, state, zip, phone#, etc.

But tblAccountHolder could have more than 1 entry for "John Doe", depending on how many different account types he has.

tblGuarantor will hold other NON-BASIC INFO for the Co-Signer, such as employer, credit rating and such.

The MASTER table with the basic information can be named whatever you wish.

cplmckenzie

 

[moishy]

cplmckenzie,

Thanks for your help. I've given considerable thought to the the assignment you gave me. I'm still not clear on what kind of data would be in the following tables:
tblAccountHolder
tblTransactionType

Attached You will find a sample db, with my progress (I hope!) so far. There may be things I will change as the development advances, but for starters this is it (minus the two tables I don't understand).
I'll be out over the weekend, but I'll get back to it in the beginning of next week.

Thanks again.

moishy

 

[cplmckenzie]

moishy,

This is just a quick test of the table layouts (with modifications and additional tables added) that you provided.

Forms have been created to permit the creating, adding and editting basic informations tables.

MainForm --- User Board will open another form with 4 buttons.

1. Clients-Guarantors-Banks will open a form for working with those data tables.

2. Accounts And Transactions is not functioning. It is there for you to determine if this is the type of information you will require.

Note.... See Data Sheet for the following table

tblAccountType ... loan, checking, savings, creditcard, others that a particular AccountHolder may have.

as 1 person may have multiple accounts, having this table will permit you to have a view of that persons total accounts on file.

The Create New Account button will allow creating a Customer New Account, with most of the data being pulled from existing tables by dropdown listboxes and user input.

Each new account will then be written to a table (tblAccounts) which will be linked to a customer from tblMaster.

This created table will permit the use of the button View Account Status.

This will allow you to see, for instance, date of last payment, next payment due date, and current account balance amongst other things.

This data view is decided by you. It will also be useful for creating the view of Account History.

If this looks useful, post suggestions and any ideas you have, then I will begin a visual layout of tables and forms and how they can be pulled together to view your data and post it.

Note....
All data in tables come from MS NorthWind database tables.

Your database with modifications are in the zipped attachment.

cplmckenzie

 

[moishy]

cplmckenzie,

First of all I apologize for the late response, I was busy putting out life's little fires all week.

I can't believe the effort and time you are putting into helping me, may you be an example to others of kindness and devotion.

Generally, it all seems good, except for the changes in tblAccountType, the only possible accounts that one may have (in our case) is loan and deposit, all the possibilities you provided aren't relevant in my specific case. (If I understood correctly that that table tracks accounts within our foundation and other commercial banks, I don't think it would be necessary for tracking commercial accounts because the only type of account we're interested in is checking accounts.

Once again thanks for your help and sorry for the delay.

moishy

 

[cplmckenzie]

moishy,

Yes, this only tracks accounts within the Foundation.

cplmckenzie

 

[cplmckenzie]

moishy,

Just posted some more mods to your database.

In Particular, have made the "Create New Account" Functional enough to test and see if it will provide the info you want.

Start by selecting "Get Customer" button.

Once customer is selected click "update Account", the Form will be auto-filled in.

Next do the same for the Guarantor, process is the same.

Before going further let me say, I noticed that in your tblAccounts design you chose to have the Guarantor SSN as a part of that table.

I left it as such, but ideally, the dataitem SSN is specific to the Entity "Guarantor".

A small drawback to the way it is now is that if, for some reason you wanted to know a Guarantors SSN, you would need to access an Account Record that he is in.

Also, and more importantly, When you are looking for and select a Guarantor,we are currently looking for their Lastname.

This could be a problem as 2 different Guarantor could have the same Lastname and incorrect information could be enter for the Guarantor.

If you were to make SSN a part of the tblGuarantor, the search could be then for Guarantor SSN, thereby assuring that the correct Guarantor is selected as the SSN is specific to 1 person.

Also, look at the note I placed in the different table design area, they somewhat explain how the table field are being linked up.

Before going further, I will wait for you to post your ideas about resturcturing the tblGuarantor so that if need be I can re code the programming for the search, and form features.

cplmckenzie

 

[moishy]

cplmckenzi,

Thanks again for the personalized attention you are giving my question.

I only put the Guarantor SSN in the tblAccountsso in can be linked but I see you have a better idea, so be it.

moishy

 

[moishy]

Are you waiting for a response?

 

[cplmckenzie]

moishy,

Not really.

Just want to be certain that the data that we are saving for tblAccounts is all that you want for each Client Account.

cplmckenzie

 

[moishy]

Yes it is.

Thanks again.

 

[cplmckenzie]

moishy,
pardon the delay. Been putting out life's little fires.

You are already aware of the client - Guarantor function and what they do.

Now run the Account Transactions functons by

Starting with opening form UserBoard
selecting the button for Accounts function and then Presently select

Create New Account

This will open a form which allows you to select a Client (from tblMaster)

Once this forms opens up ... in the first list box you began typing a client LastName (which you have already created before hand in the Previous Client - Guarantor functions)

As you type in this text box the listbox selection will narrow down your Client choices.

NOTE...In order to test this you can view the table tblMaster and tblGuarantors to know the names of existing records.

Once the listbox displays the correct Client Name then DOUBLE-CLICK the name.

A form will open display all relevant data for that client.... then click Update Button.

The selected record will then be added to the New Account form for the client.

Next do the same for the Guarantor by clicking the Guarantor button.

Go through the same process as you did for the Client.

Once the data for the Guarantor has been completed and Updated then you need to MANUALLY enter data for each account in the bottom section of the New Account form.

The data that you enter here plus data selected for Client and Guarantor will be written ta to tblAccounts.

This will permit you to show things such as.

Account Status
Payments Made
When next payment is due
Client Payment History

And many other things relevant to the Client and one or all of his accounts on file.

moishy, test this function because we will want to make sure that all needed data is being captured for future use.

NOTE.... there is currently a bug in the Guarantor function which will be rectified when final Error-Trapping is done.

cplmckenzie

 

[cplmckenzie]

moishy,

To began entering data Manually place the cursor in the GuarantorID textbox and click all this is updated automatically from the previously enter data.

Then enter the other account data.

cplmckenzie