Trying to Create a Loan Database and am lost. Before i import data I would like advice on how to si

[jashua84]

My database is going to track my Contacts. Each contact (Client, Real Estate Agent, Title Agent etc) has some relation to a loan. There can be more than one client on a loan (up to 3) and there can be more than 1 Agent. There can ONLY be 1 Title, Closing, and Insurance Agent though.



The loans table contains (loan amount, rate, term, loan program(linked to subtable) etc.)

Active Loans need to be filtered onto my "pipeline". Once they close they do not need to be on there. The pipeline tracks milestones (dates of appraisal order/received, underwriting approval, and notes).

A call log of the calls I've made and ones that need to be done - and a master calendar that hold (upcoming birthdays, most recent loan anniversary, due dates from pipeline).

There is a Marketing tab that relates to all contacts. I would like to be able to filter clients with active loans and apply them to one campaign, then filter closed loans by a certain criteria and loan type and send out marketing that way.

I know I need a welcome screen (like a homepage) which I created with buttons to each of the things I would want to do.

I send out a TON of mailing so printing labels from the contacts is a must, and e-mail functionality.

My problem lies in migrating my excel data over. I have most of this data in 1 excel spreadsheet but every time I try and parse it out ... I end up with a bunch of useless data. I've read (well attempted to) access bible 2013, and paid for UI templates, nothing is turning out. So I figured I'd give this forum a shot. This is the database I have now. I tried to parse together the templates that come with office because that has gotten me the furthest and I like the direction it's going. I consider myself a smart guy and feel like the first steps I need to do are break out the common fields in the contacts (address fields etc) like I did with the state but at this stage I'm afraid i'll remove the wrong thing and everything will collapse!

[June7]

Don't think I would have separate agent tables. Only one table and if a person really can have only one role, another field that defines the role. Same for Contacts and tbl_BusinessContacts. Why 2 tables?

Multiple similar name fields for same type of data indicates a non-normalized structure. tbl_Loan allows 2 clients - what if you need 3 or more?

tbl_ContactType is not linked to anything.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

Be consistent with naming convention. Contacts and Tasks tables do not have the tbl_ prefix

[jashua84]

DataHelpV2.zip
here is what is started from scratch. Is this more normalized? I tried to create a temp form and it was a pain to enter borrower data. I wanted a form to enter it into all of the applicable tables but it wouldn't let me add the name until I added an address, and it wouldn't let me add an address until I selected a state. etc. Is there a way to get around that? And when I finally did generate a form it was just bank. Even after adding all of the fields I wanted, they display in design mode but don't view in form mode. I appreciate your quick response! I know this is a lot to ask, but I really do want to learn. That and my employment is pretty much contingent on getting this to work. So again, thank you!

Can you provide tips on how to build my forms going forward? And how to I create a dashboard that opens right away a user can work on. My goal is to never have the user touch the tables, rather enter information/search information in the forms.

Also what is the best method for searching a datasheet on a form? I would like to have the option (like a google searchbox) in the top of all of my forms that you can search an entire record (and related tables) for information.

[jashua84]

Oh! I also am stumped on how to loop the borrower back to the loan. do I do it through borrowerdetails and then to borrowerposition and then to loan? Some loans only have 1 borrower, others have more. I want to normalize my data and am trying to figure out the best way to do this.

[jashua84]

and due to this being me either pulling it off and having a job, or utterly bombing I will do whatever it takes to this this working, I am not even beying compensating sometone, but I do need to learn how to make it so I would prefer to speak with someone who could walk me through what they do. And I can pay it forward.

[June7]

If each loan can have multiple borrowers and each borrower can be associated with multiple loans, this is a many-to-many relationship and requires a junction table: tblLoanBorrowers.

Should each call be related to a loan?

A borrower is a type of contact. Not sure really need separate table. Actually, tbl_BorrowerDetails could be the junction table between tbl_Loan and tbl_Contact

Could a borrower be a company?

The Notes table will likely be problematic. For instance, each loan can have only one associated record in tbl_Notes. Instead of one Notes table, probably best that tbl_Loan, tbl_Calls, tbl_Pipeline, tbl_AgentDetails each have their own related Notes table. Then the ID from the parent tables will be saved as fk in Notes tables. This allows multiple notes.

Misspelled veteran as veterin in VeterinStatus.

[jashua84]

The spelling got a little creative last night. I apparently took a go at the database on ambien, and spelling is not my strong suit.

so with the notes ... you would have a tbl_Pipeline linked to tbl_PipeLineNotes by pk_PipeLineNotesID and then link pk_PipeLineNotesID to the tbl_Notes? So there would be 3 additional "notes" tables that don't actually contain notes, but refer back to the main notes table? I am just trying to normalize my data as much as possible per your earlier instructions but may not be grasping the concept.

And I think the call should be related more to the contact not the loan. since all loans are related to a contact this would indirectly relate them right? There will be calls to contacts who were associated with a loan but the call may be unrelated.

And for my particular situation a borrower would not be a company. We don't do commercial lending.

Also I have been reading a lot about yes/no fields and that they can be problematic later down the time. Is it best to change VeteranStatus to short text and then type in Yes or No? But if I do this, then I would want to connect it to a yes/no table right? Thank you! I am starting to get my toes wet as soon as I figure out what on earth I'm doing wrong on the forms i'll repost my changes and see what you think.

[June7]

No, I am saying tbl_Notes would not exist. Unless this is a lookup table of standardized note items to be selected. I assumed notes would be free-form text entry.

VeteranStatus as a Yes/No should be fine. The issue with Yes/No field is having a bunch of them where there should be one field with multiple choices. For example, a db that identifies kinds of pets in a household. Table with a yes/no field for each kind of pet (Cat, Dog, Bird, etc) is not a normalized structure and will cause big problems.

For simple multiple choices where the list is short and choices would never change (Yes/No, Male/Female, Good/Fair/Poor) I would not always use a lookup table. Option is combobox ValueList and save the descriptive text.