Solved

Problem in restablishing relationships

Posted on 2011-09-19
13
290 Views
Last Modified: 2012-05-12
Experts,
I have a table called tblLoans. One of the fields is labeled LoanStatusID and it gets data from a supporting table called tblLoanStatus.

There can be many different types of loan statuses. For example, a loan could be open, pending or in bankruptcy. There can only be one status for a loan at any point in time, however the status of loan can change. For example a loan could be pending and then become open.

Let’s say there is a loan with a Pending status. There are many reasons for a Pending loan.

I considered the following design:
tblLoans
LoanStatusID

tblLoanStatus
LoanStatusID (PK)
PendingLoanID (FK)

tblPending
PendingID (PK)
Pending

tblOpenPendingItems
OpenPendingItemsID (PK)
PendingReason

This is as far as I get and need help going forward. I need to track the LoanStatus so I suspect at least another table and some work on the above. Please keep in mind that Loan Status could be something besides Pending, e.g. Open, Close, Bankruptcy, Repossession, Place for Collections, etc.


0
Comment
Question by:Frank Freese
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 7

Assisted Solution

by:BusyMama
BusyMama earned 25 total points
Comment Utility
How about something like this, where you keep all of the statuses in one table, with dates and reasons?


tblLoans
LoanID

tblLoanStatus
LoanStatusID (FK)
LoanID (FK)
LoanStatusBeginDate
LoanStatusEndDate
LoanStatusReason

tblLoanStatusLookup (optional - contains the loan status definitions)
LoanStatusID
LoanStatusDescription
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
You want to run along the lines of what BusyMama suggested:

1 Table for the loans
1 Table for lookup of the different types of statues involved.
1 Table for recording when a loan status changes.

  I might make the last a bit more generic by doing:

tblAuditTrack
AuditID - PK
AuditDateTime
Table - Table name
RecordKey - PK of record that was changed.
FieldName - Name of field changed
NewValue
ChangedBy

 There are lots of examples floating aorund of that.  I would not record a start/end date as then you get into overlapping time spans between records, which only complicates things.

Jim.
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 25 total points
Comment Utility
how about adding a date field to your table tblLoanStatus to have a historical information of a loan status.
this would mean that each time an status change of a loan occurs, you will be adding record for that particular loan in tblLoanStatus with the current date.

your table structure will be


tblLoans
LoanID

tblLoanStatus
LoanStatusID (pK)
LoanID (fk)
StatusID (FK)
StatusDate

tblStatus
StatusID
Description


0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
sorry, did not refresh...
0
 

Author Comment

by:Frank Freese
Comment Utility
I have in tblLoans LoanOriginatingDate.

I can see how under tblLoanStatus where we connect to tblStatus. I could always add the StatusDate. But let's say that the loans Status is Pending and the reason for Pending might be lack of proper identification. The user needs to see why the loan was Pendingv for there can be many reason why a loan was pending.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<But let's say that the loans Status is Pending and the reason for Pending might be lack of proper identification. >>

  Add "StatusReason" to tblLoans as a free text field, or you might decide to do another lookup table (which I would suggest).

  You would also track that as a field that might change.

Jim.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Frank Freese
Comment Utility
Jim,
I kind-of-understand the direction your going but somewhat confused on how this record would look?

tblAuditTrack
AuditID - PK
AuditDateTime
Table - Table name
RecordKey - PK of record that was changed.
FieldName - Name of field changed
NewValue
ChangedBy
0
 
LVL 7

Expert Comment

by:BusyMama
Comment Utility
This is fun ... :)  I like it when there are multiple ideas; although these are kind of similar.

Does the Pending "reason" ever change without the status changing?
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 450 total points
Comment Utility
Your loan table would look like this:

tblLoans
LoanID - PK
LoadOrigDate - Date/Time
LoanStatusID - FK to tblLoanStatuses
LoanStatusReason - FK to tblLoanStatusReasons

tblLoanStatuses
LoanStatusID - PK
LoanStatusDesc - Text

1   Open
2   Pending
3   Closed
4   Bankruptcy
5   Repossession
6   Placed for Collections

tblLoanStatusReasons
LoanStatusReasonsID - PK
ReasonDesc - Text

1  Credit check
2  Income verification
3  Incorrect facts on loan application
4  Missed Payments
5  Abandoned property

  I suggest a lookup for the reason that someone may ask for a given status why that status was assigned.  For example on a Repossession, it may be due to missed payments or abandonment.  If you leave the reason free text in tblLoans, someone might do:

Abandonement.
Abd.
They left.

  Trying to report off that would be impossible.  However if a lookup, you may also have to address they fact that not every reason can be applied to every type of status.  

  Now for the change.  New loan is created.  So change history reads:

AuditID / Time           table        PK   field              New value   changed by
1   09/19/11 12:01    tblLoan    1   LoanStatus    1                  134

Now the loan process moves forward, for a credit check first and then an income check, so the audit table now looks like this:

AuditID / Time           table        PK   field                New value   changed by
1   09/19/11 12:01    tblLoan    1   LoanStatus      1                  134
2   09/19/11  13:00   tblLoan    1   LoanStatus      2                  934
3   09/19/11  13:00   tblLoan    1   StatusReason  1                  934
4   09/19/11  13:45   tblLoan    1   StatusReason  2                  575
5   09/19/11  15:00   tblLoan    1   LoanStatus      3                  755
6   09/19/11  15:00   tblLoan    1   StatusReason  3                  755

  Can you follow through what happened?

  That's one way of structuring it.  There are a number of ways to do it.  For example, if you wanted to audit just this one table, then instead of one field per audit record, you might decide to just save a copy of the entire loan record in the audit table as one record.

  You might also keep a "version number" in the loan table so audits could be grouped:

AuditID / Time           table        PK   Version   field                New value   changed by
1   09/19/11 12:01    tblLoan    1     1            LoanStatus      1                  134
2   09/19/11  13:00   tblLoan    1     1            LoanStatus      2                  934
3   09/19/11  13:00   tblLoan    1     2            StatusReason  1                  934
4   09/19/11  13:45   tblLoan    1     3            StatusReason  2                  575
5   09/19/11  15:00   tblLoan    1     4            LoanStatus      3                  755
6   09/19/11  15:00   tblLoan    1     4            StatusReason  3                  755

  incrementing it each time the loan record as saved.  

   There are no right or wrong answers here in terms of design, just what will work best for you and the problem at hand.

Jim.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 450 total points
Comment Utility
Oops, messed that last up slightly:

  You might also keep a "version number" in the loan table so audits could be grouped:

AuditID / Time           table        PK   Version   field                New value   changed by
1   09/19/11 12:01    tblLoan    1     1            LoanStatus      1                  134
2   09/19/11  13:00   tblLoan    1     2            LoanStatus      2                  934
3   09/19/11  13:00   tblLoan    1     2            StatusReason  1                  934
4   09/19/11  13:45   tblLoan    1     3            StatusReason  2                  575
5   09/19/11  15:00   tblLoan    1     4            LoanStatus      3                  755
6   09/19/11  15:00   tblLoan    1     4            StatusReason  3                  755
0
 

Author Comment

by:Frank Freese
Comment Utility
folks,
since we are having soooooo much "fun" I need a little time to study this.
0
 

Author Comment

by:Frank Freese
Comment Utility
by the way, I use lookup tables as much as possible. I've been tasked with developing a new db from off of one that was macro driven, no code, no keyed fields, no business rules, no relationships, few fields not labed 1,2,3,4, adding values then storing them in tables, etc  so you can imagine the garbage in and out being experienced. Ever try adding text? They tried. Yes, a pending reason can change without Pending itself changing.
0
 

Author Closing Comment

by:Frank Freese
Comment Utility
thanks folks - wish I had 1,000 pts to give. I truly appreciate everyones input and can take this forward now.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now