Normalizing help in a Job Tracking Database

[iuianj07]

Hello guys,

I need expert advice/guidance with how I should properly normalize my database:

Basically, we have a Job Tracking Database, which main purpose is to track the current phase of a job that we do... Our client is a bank, and they send us a batch of loans that we should analyze for them... for each loan that we analyze, it goes through various phase/stage: (date format)

Entry Completed
OSAR Analyst Completed
QC (Quality Control) Completed -- 'to double check the work
Sent Back to Analyst -- ' if QCer found mistake from analysis
Re-submit for QC -- 'after Analyst made corrections
Signed Off -- 'when we returned the loan to the client
On Hold for Issue --'if we can't complete analysis and need to put on hold for clarification


Currently, the table structure that we have is that there is a date field for each phase above, example: (this does not include all fields on the table, just fields that pertain to loan status)

tblJobTracking
JobTrackingID
ReportingPeriod
InvestorNumber
LoanNumber
DateAssigned
EntryAnalyst -- 'a user who is assigned to enter
EntryAnalystCompleteDate --'date
OSARAnalyst --'another user
OSARAnalystCompleteDate
QCAnalyst --'QCer to check the work of Analyst
ReturnedToAnalyst --'date
ResubmitToQC --'date
QCerCompleteDate --
SignedOff --'highest level of QC to check and submit back to client
SignedOffDate --'date the loan is totally completed
OnHoldforIssue --'Whenever we can't complete loan


I thought that this table structure should be normalized properly, but can't figure out how is the correct way.

I thought of having a table for the status changes? kinda like:

tblStatusChange
StatusID Status
1 Entry Completed
2 Osar Completed
3 QC Completed
4 Sent Back to Analyst
5 Resubmit for QC
6 Signed Off
7 On Hold For Issue

tblLoanStatus
LoanStatusID
JobTrackingID
Analyst -- 'dropdown list of users (Entry Analyst,OSAR Analyst, QC Analyst...)
StatusID
StatusDate
StatusComment


With this kind of table structure, then we could have a minimum of 3 records for each loan(tblLoanStatus)? there could be a possibility to only have one record per loan, just Update the StatusID every time it goes to another phase, but the problem with that is, if a loan has already been signed off, and for some reason, we want to know when was that loan got OSAR Completed, then there's no way to know or query when it got completed, that's why it is safe to have multiple rercords for each loan? But is that the best approach? Doesn't it cause to have too much redundant data?

Please I need advice and guidance on how I should work on these...

Any help is greatly appreciated.

Thank you...

 

[PHV]

it is safe to have multiple rercords for each loan
Indeed, as it's a many-to-many relationship.
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

Hello PHV,

Thanks for the article,

So is the above table structure the correct way? Or do you have better suggestions? With the above table structure, then on the form, there will be a sub-form that is bound to tblLoanStatus, then set as continuous form, so that a loan goes to another phase, it'll create another record?

Thanks for your help

 

[PHV]

So is the above table structure the correct way
I think so, provided that tblJobTracking don't have the Analyst,Date columns for each phase.

Main form bound to tblJobTracking
subform bound to tblLoanStatus + tblStatusChange, linked to main form via JobTrackingID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[iuianj07]

Thanks PHV,,

I have a follow up question though... How about when I add an tblOnHold, which is used whenever a loan we do couldn't be completed and needs to put on hold and wait for clarification from our client... How should I create the relationship for tblOnHold? I was thinking

(what could the tblOnHold look like)

tblOnHold
OnHoldID
JobTrackingID
OnHoldDate
OnHoldComment
VendorConsultant


Should I put LoanStatusID on tblOnHold? That for when a loan from JobTracking has StatusChange "7" then it should show up on tblOnHold?

I hope I made sense, if not let me know...

any help is greatly appreciated

Thank you...

 

[dhookom]

PMFJI but why do you need a table for Onhold? Isn't this simply a status update in an existing table?

Can you share your current table structure?

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

Hello dhookom,

what does PMFJI mean?

anyway, with regards to OnHold just simply being a status update... we still need to track the OnHold issue to/from the client... this is the table structure (may not be properly normalized yet) for Issue_Log table (tblOnHold)

Issues_Log

IssuesID
JobTrackingID
IssueNumber -- 'this comes from the client whenever they resolve an issue
AssignedVendor
IssueOriginator
IssueOpenDate
WFBStatus --'if Open or Closed
VendorConsultant
Top20
UW
IssueStatus -- 'New, Open or Closed
IssueCloseDate
NextAction -- 'If Client or Us(vendor)
VendorStatus -- 'New,Open or Closed
VendorLastUpdateDate
VendorLastSubmitDate


We run reports from this table, to send to client with regards to loans that has been put on hold...

Thanks for helping

 

[dhookom]

I was assuming you had normalized your table structure so that each task/status update would create a new record in a related table as PH pointed to.

If you want to keep your current structure, why do you not just change the IssueStatus to "On Hold"?

Duane
Hook'D on Access
MS Access MVP