Hello guys,

I am currently in the process or re-structuring one of our current databases. We currently have an aweful unnormalized table structure that I changed the table structure to hopefully a properly normalized table structures

I think I did an okay job in normalizing the tables, I am now in the process of updating the data from the unnormalized to the normalized tables...

To have a quick overview, this is a simple Job Tracking database, wherein our client is a bank, and they send us loans that we need to review and complete for them... the main purpose of this database is to keep track of all the loans we received and what phase has it already been... (phase which mostly are dates.. meaning -- loan received, loan assigned, loan entry completed, QC completed, loan sent back to client -- meaning it has been completed, loan invoiced, loan On hold, etc...)

Now my main question... is there a quick way for me to update the normalized table structure from the unnormalized table? The db currently have 4,700+ records and it'll be difficult to manually update the normalized table structure...

Here is the db's old unnormalized table structure and my new normalized table strucure: -- this could be long...

UnNormalized table

tblLeaseTracking

Situs ID
Turnaround
Date Assigned to Analyst
Situs Analyst
Date Entry Complete
Process Document Issue
Process Document Issue Comment
Date Process Document Issue Sent to Client
Date Process Document Issue Resolved
Date Invoiced
Loan Number
Tenant Name
Lease Type
Lease Additional Info
Client Office
Date Received
Lease Documentation
Property Operating Statement
Rent Roll
Property Inspection
OSAR
Tenant Financials
Other Documentation
Initial Receipt Document Issue
Initial Receipt Document Issue Comment
Date Initial Receipt Issue Sent to Client
Date Initial Receipt Issue Resolved
Rush Deal
Situs QC Analyst
Date QC Complete
Date Submitted to Client
Lease Consent Withdrawn
Lease Consent Percentage
Lease Consent Withdrawn Comment
WFB Asset Administrator
Current Turnaround Date
Link to Folder
Internal Comments
Rent Reduction
Space Reduction
Below Weighted Average Rent
Above Weighted Average Rent
Term Extension
Relocation
Early Termination
Short Term Lease
National T
Anchor T
Property Type
Other
Expansion
EnterTime
RushSameDay/1day
Rush2-5day
ResolvedTime

NORMALIZED TABLE STRUCTURES:

tblJobTracking
SitusID -- PK
LoanNumber -- Text
TenantName -- Text
LeaseType -- FK to tblLeaseType
LeaseAdditionalInfo
ClientOffice -- FK to tblClientOffice
ClientAssetAdministrator -- FK to tblClientAdministrator
LinkToFolder -- Hyperlink
InternalComment -- Memo
RushDeal -- Yes/No
Rush2to5Days -- Yes/No
PropertyType -- FK to tblPropertyType
DateInvoiced -- Date/Time

tblDocumentTracking -- purpose: to track if the client sent us  the important documents needed for us to review the loan

DocumentTrackingID -- PK
SitusID -- FK to tblJobTracking
LeaseDocumentation -- Yes/No
PropertyOperatingStatement -- Yes/No
RentRoll -- Yes/No
PropertyInspection -- Yes/No
OSAR -- Yes/No
TenantFinancials -- Yes/No
OtherDocumentation -- Memo

tblLeaseAttributes -- users will check if what kind of lease attribute the loan they are reviewing.

LeaseAttributesID -- PK
SitusID -- FK to tblJobTracking
RentReduction -- Yes/No
SpaceReduction -- Yes/No
TermExtension -- Yes/No
Relocation -- Yes/No
etc...

tblLeaseType
LeaseTypeID -- PK
LeaseType -- what kind of lease is being reviewed (ex. Amendment, New Lease)

tblLoanStatus
DealStatusID -- PK
SitusID -- FK to tblJobTracking
Status -- FK to tblStatusChange
Analyst
StatusDate -- Date/Time
StatusComment -- Text

tblPropertyType
PropertyTypeID -- PK
PropertyType -- what kind of property are they reviewing (office, retail etc...)

tblQualityTracking -- to check quality of work of the entry analysts by the QC Analysts

QualityTrackingID -- PK
SitusID -- FK to tblJobTracking
RentalAmounts -- Yes/No
LeaseTerm -- Yes/No
etc..

tblStatusChange -- different phase/status that a loan goes through

StatusChangeID -- PK
Status -- Text

tblClientOffice
ClientOfficeID -- PK
ClientOffice -- Text (ex. West office, East office)

tblClientAssetAdministrator
ClientAssetAdministratorID -- PK
ClientAssetAdministrator -- Text

As  you can see, I've managed to create 10 different tables from
the one giant table.

My problem now is how I could update the data from the unnormalized to the normalized data...

I could give an idea of how it should be updated:

UnNormalized

SitusID LoanNumber  TenantName  DateEntryComplete DateQCComplete
111      222         Tname1      7/1/2011          7/3/2011

DateSubmittedtoClient
7/3/2011

Normalized

tblJobTracking

SitusID  LoanNumber  TenantName
111      222         Tname1

tblLoanStatus

DealStatusID  SitusID  Status  Analyst  StatusDate  StatusComment
1             111      1       Name1    7/1/2011
2             111      2       Name2    7/3/2011
3             111      3                7/3/2011    Sent Back


I apologize for the lengthy post,

Any help or guidance is greatly appreciated.

Thank you