Linking/Matching multiple IDs

[brownk]

Hello everyone! Im new to this forum as well as databases in general. Right now Ive got a little project going on where i have to make a commercial loan database generate automated reports at the end of the month that lists all the new applications with different information on them. Ive gotten almost everything done except for linking the applicants name to the report in my query. Im pulling data from 6 tables which are all linked by applID. My problem is none of these tables that contain applID have the applicants name. The table that DOES have the applicants name is not organized by applID; its organized by borrowerID within my Borrower table, which does not contain any applID. I have one other table who's purpose is to put an applID to a borrowerID. I need some help in somehow linking those two within my query. Any help would be greatly appreciated. Thanks!

[jzwp11]

Welcome to the forum!

I'm not following what you are saying; perhaps you can provide some example data? Are you saying that the applicant's name is a field in the borrower's table? Can more than one applicant be tied to a borrower? In what table are both applID and the applicant's name fields located?

What type of information about the applicant is spread out over the 6 tables you mention?

[brownk]

Sorry for being so vague, ill try my best to explain this lol. There can be only one applicant per applID. My tables are:
Application; this contains the applID, the status of the application, the date, and status.
Collateral; applID, collateral type, value.
Loan: applID, name, loan type, interest, ect..
Decision: applID, decision, comments.
Decision Reasons: applID, reason.
ApplicationID_BorrowerID; This is the table that puts a borrowerID to a applID.
Borrower: borrowerID, Borrowers name. This field does NOT contain an applID.

The applicants name is only found in the borrower table, and i cannot pull data from this table using the applID. This is where my problem resides. Im hoping there is a simple way to associate the applID, which is spread out through every other table, with the borrowerID.

[jzwp11]

Are the borrower and the applicant the same person?

[brownk]

Yes they are the same person.

[jzwp11]

Based on what you have provided so far, your tables are not structured properly.

A borrower/applicant, can apply for many loans, so that describes a one-to-many relationship

tblBorrower
-pkBorrowerID primary key, autonumber
-txtApplicantNumber (some number you assign, that has relevance to your operation)
-txtFName
-txtLName


tblLoans
-pkLoanID primary key, autonumber
-fkBorrowerID foreign key to tblBorrower (must be a long integer number datatype)
-txtLoanName
-longTerm (a number representing the number of months or year of the loan)
-fkTermIterval foreign key to tblTermIntervals
-spRate (interest rate of the loan)
-currLoanAmount

tblLoanEvents (any event related to the loan process)
-pkLoanEventID primary key, autonumber
-fkLoanID foreign key to tblLoans (must be long integer number datatype)
-fkEventID foreign key to tblEvents
-dteEvent date of the event
-txtEventDescription

tblEvents (holds all events that are applicable in your loan process such as application, review, accept, deny etc.)
-pkEventID primary key, autonumber
-txtEvent


Since the collateral is dependent on both the borrower and the loan for which they are applying, the logical join is to the loan table not directly to the borrower table

tblLoanCollateral
-pkLoanCollateralID primary key, autonumber
-fkLoanID foreign key to tblLoans
-fkCollateralTypeID foreign key to tblCollateralTypes
-currCollateralValue

tblCollateralTypes (a table to hold a list of all possible collateral types)
-pkCollateralTypeID primary key, autonumber
-txtCollateralTypeName







tblTermIntervals (a record for each allowable term interval you use: days, weeks, months, years )
-pkTermIntervalID primary key, autonumber
-txtTermInterval

[brownk]

Ive been thinking that it was not structured properly. I did not form this database Ive just been assigned to it. Thank you for your input.

[jzwp11]

I hope you intend to restructure it, otherwise you will have difficulty with it for as long as you have responsibility for it.

[brownk]

I got my query to work. It was very simple, all i had to do was link borrowerID with the applID from the first table and change some of my joins to be left joins. Now my report is generating just fine. I talked to management and there is plans to restructure this database.

[jzwp11]

Glad to hear that you got the query working & now you also have a head start on the restructuring.

[Julieve]

Hi..
I was interested to read this post and your reply..
How would the structure change if there were multiple businesses loaning funds on a short term basis to multiple borrowers with interest applied when the loan was repaid in full..
I have setup a table of business entities which could be either borrowers or lenders, but Im stumped on how to structure the transactions in a way that I could run reports for a borrower or a lender to get a loan statement showing transactions..

Your suggestion would be greatly appreciated..
THanks

[jzwp11]

Hi Julieve,

For each loan is there only 1 borrower and 1 lender or can a loan consist of more than 1 borrower and/or more than 1 lender?

[Julieve]

Yes.. One borrower and one lender.. I have setup a transaction table with the borrower, lender, date, amount, calculated code combining borrower and lender code.. I thought then I could use the code to query transactions between the same lender and borrower..

[jzwp11]

I would probably have a table to hold the Loan basics and a related table for the transactions since you can have many transactions per loan. Since each loan has two parties (lender & borrower) technically speaking, that would be a one-to-many relationship which would typically require a separate but related table.

tblLoans
-pkLoanID primary key, autonumber
-dteLoan date of loan
-currLoanAmt
other loan related fields

tblLoanParties
-pkLoanPartyID primary key, autonumber
-fkLoanID foreign key to tblLoans
-fkPartyID foreign key to table that holds the borrower & lender info
-fkPartyTypeID foreign key to a table that holds the type of Party (tells whether the party is either the borrower or lender)

tblPartyTypes (2 records in this table, "borrower" and "lender"
-pkPartyTypeID primary key, autonumber
-txtPartyType

tblLoanTransactions
-pkLoanTransID primary key, autonumber
-fkLoanID foreign key to tblLoans
-currTransAmt amount of transaction
-dteTrans Date of transaction

[Julieve]

That was a great help.. thankyou
I wasnt thinking of splitting the party type data up into tables but I can see how that would help in queries and reporting.. Now just have to work out how to the get the forms and reports setup.