hello guys,
I am re-structuring one of our database which has an unnormalized table structure... I am now at the point of re-creating the queries from the unnormalized structure to the normalized one:
Basically this database is a job tracking database, wherein we receive loan records from our client for us to review, and we use the database to track the status/phase of the loan (ex. received, assigned to analyst, analyst entry complete, QC complete, sent back to client)
The old table structure is below:
LeaseTracking
SitusID -- PK
LoanNumber
TenantName
DateReceived -- Date we receive the loan
DateSubmittedToClient -- Date we completed the loan and send back the loan to the Client
ClientAssetAdministrator
DateProcessSentToClient -- Date when we put a loan on hold
DateProcessResolved -- Date when the loan that was put on hold has been resolved
ProcessDocumentIssueCommnent -- Comment for loan on hold
DateAssignedToAnalyst -- Date an Analyst started working on the loan
SitusAnalyst -- Analyst Name
DateEntryComplete -- Date the Analyst completed the loan
ClientOffice -- Just the client office branch (east or west)
QCAnalyst -- Quality Control, a reviewer to check the work of the Analyst
DateQCComplete -- Date the QC finished reviewing the Analyst work
LeaseConsentWithdrawn -- this is Yes/No, whenever a loan has been withdrawn
As you can see this is a bad table structure. I've fixed this and tried to normalize the table properly (because I've learned so much here in Tek Tips)
New table structure:
tblJobTracking
SitusID -- PK
LoanNumber
TenantName
LeaseTypeID -- FK to tblLeaseType
LeaseAdditionalInfo
ClientOfficeID -- FK to tblClientOffice
ClientAssetAdministratorID -- FK to tblClientAssetAdministrator
PropertyTypeID -- FK to tblPropertyType
tblLeaseType
LeaseTypeID -- PK
LeaseType -- (ex. Lease, Amendment)
tblClientOffice --
ClientOfficeID -- PK
ClientOffice -- ex. East or West
tblPropertyType
PropertyTypeID -- PK
PropertyType -- ex. Retail, Office, Multi Family etc
tblLoanStatus
DealStatusID -- PK
SitusID -- FK to tblJobTracking
Status -- FK to tblStatusChange
Analyst
StatusDate
StatusComment
tblStatusChange
StatusChangeID -- PK
Status -- ex. Received, Assigned, Entry Completed, QC Completed, Submitted to Client, Withdrawn etc..
Now as you could see, I was at least able to normalize the table structure, even if its not the perfect normalized table structure, it is way better than it originally was.
Now with the new table structure, I have to re-create all queries that users use, one of which is the query below (yes field names were horrible too, I've fixed it in the new table structure):
With the query above, I tried to replicate the query but using the new table structure:
although I am not sure if this even has the same functionality as the first query.. can you please double check?
I have a problem on how to re-write the query for the field name DateSubmittedToWF (below) since it uses an iif statement to check if the Lease Consent Withdrawn checkbox is checked on the old table structure, but in the new table structure, there will be a loan status record for withdrawn status.
IIf(IsDate([Lease Tracking]![Date Submitted to WF]),[Date Submitted to WF],IIf([Lease Tracking]![Lease Consent Withdrawn]=0,"In Process","Withdrawn")) AS [Date Submitted to WFB],
And also the WHERE clause from the old query that is:
((([Lease Tracking].[Client Office])="East") AND (([Lease Tracking].[Date Submitted to WF]) Between [First Day of the Month] And [Last Day of the Month])) OR ((([Lease Tracking].[Client Office])="East") AND (([Lease Tracking].[Date Submitted to WF]) Is Null))
on the part that should also return records that has yet to be submitted...
I know this could be a handful, but I would appreciate anyone's help... I have been working and re-writing this query several ways and what I posted is what I think is closest to what I think the should be..
Thank you again...
I am re-structuring one of our database which has an unnormalized table structure... I am now at the point of re-creating the queries from the unnormalized structure to the normalized one:
Basically this database is a job tracking database, wherein we receive loan records from our client for us to review, and we use the database to track the status/phase of the loan (ex. received, assigned to analyst, analyst entry complete, QC complete, sent back to client)
The old table structure is below:
LeaseTracking
SitusID -- PK
LoanNumber
TenantName
DateReceived -- Date we receive the loan
DateSubmittedToClient -- Date we completed the loan and send back the loan to the Client
ClientAssetAdministrator
DateProcessSentToClient -- Date when we put a loan on hold
DateProcessResolved -- Date when the loan that was put on hold has been resolved
ProcessDocumentIssueCommnent -- Comment for loan on hold
DateAssignedToAnalyst -- Date an Analyst started working on the loan
SitusAnalyst -- Analyst Name
DateEntryComplete -- Date the Analyst completed the loan
ClientOffice -- Just the client office branch (east or west)
QCAnalyst -- Quality Control, a reviewer to check the work of the Analyst
DateQCComplete -- Date the QC finished reviewing the Analyst work
LeaseConsentWithdrawn -- this is Yes/No, whenever a loan has been withdrawn
As you can see this is a bad table structure. I've fixed this and tried to normalize the table properly (because I've learned so much here in Tek Tips)
New table structure:
tblJobTracking
SitusID -- PK
LoanNumber
TenantName
LeaseTypeID -- FK to tblLeaseType
LeaseAdditionalInfo
ClientOfficeID -- FK to tblClientOffice
ClientAssetAdministratorID -- FK to tblClientAssetAdministrator
PropertyTypeID -- FK to tblPropertyType
tblLeaseType
LeaseTypeID -- PK
LeaseType -- (ex. Lease, Amendment)
tblClientOffice --
ClientOfficeID -- PK
ClientOffice -- ex. East or West
tblPropertyType
PropertyTypeID -- PK
PropertyType -- ex. Retail, Office, Multi Family etc
tblLoanStatus
DealStatusID -- PK
SitusID -- FK to tblJobTracking
Status -- FK to tblStatusChange
Analyst
StatusDate
StatusComment
tblStatusChange
StatusChangeID -- PK
Status -- ex. Received, Assigned, Entry Completed, QC Completed, Submitted to Client, Withdrawn etc..
Now as you could see, I was at least able to normalize the table structure, even if its not the perfect normalized table structure, it is way better than it originally was.
Now with the new table structure, I have to re-create all queries that users use, one of which is the query below (yes field names were horrible too, I've fixed it in the new table structure):
Code:
SELECT [Lease Tracking].[Loan Number], [Lease Tracking].[Tenant Name], [Lease Tracking].[Date Received], [Lease Tracking].[Date Process Document Issue Sent to Client], [Lease Tracking].[Date Process Document Issue Resolved], IIf(IsDate([Lease Tracking]![Date Submitted to WF]),[Date Submitted to WF],IIf([Lease Tracking]![Lease Consent Withdrawn]=0,"In Process","Withdrawn")) AS [Date Submitted to WFB], [Lease Tracking].[WFB Asset Administrator], [Lease Tracking].[Process Document Issue Comment]
FROM [Lease Tracking]
WHERE ((([Lease Tracking].[Client Office])="East") AND (([Lease Tracking].[Date Submitted to WF]) Between [First Day of the Month] And [Last Day of the Month])) OR ((([Lease Tracking].[Client Office])="East") AND (([Lease Tracking].[Date Submitted to WF]) Is Null))
ORDER BY [Lease Tracking].[Date Received];
With the query above, I tried to replicate the query but using the new table structure:
Code:
SELECT tblJobTracking.SitusID, tblJobTracking.LoanNumber, tblJobTracking.TenantName, (Select StatusDate FROM tblLoanStatus WHERE Status = 1 AND tblLoanStatus.SitusID = tblJobTracking.SitusID ORDER BY StatusDate;) AS [Date Received], (Select StatusDate FROM tblLoanStatus WHERE Status = 7 AND tblLoanStatus.SitusID = tblJobTracking.SitusID;) AS [Date Process Document Sent to Client], (Select StatusDate FROM tblLoanStatus WHERE Status = 8 AND tblLoanStatus.SitusID = tblJobTracking.SitusID;) AS [Date Process Document Resolved], (Select StatusDate FROM tblLoanStatus WHERE Status = 9 AND tblLoanStatus.SitusID = tblJobTracking.SitusID;) AS [Date Submitted to WF], tblJobTracking.WFBAssetAdministrator, tblJobTracking.WellsFargoOffice
FROM tblJobTracking
WHERE (((tblJobTracking.SitusID) In (SELECT SitusID FROM tblLoanStatus WHERE Status = 9 AND StatusDate Between [First Day of the Month] AND [Last Day of the Month];)));
although I am not sure if this even has the same functionality as the first query.. can you please double check?
I have a problem on how to re-write the query for the field name DateSubmittedToWF (below) since it uses an iif statement to check if the Lease Consent Withdrawn checkbox is checked on the old table structure, but in the new table structure, there will be a loan status record for withdrawn status.
IIf(IsDate([Lease Tracking]![Date Submitted to WF]),[Date Submitted to WF],IIf([Lease Tracking]![Lease Consent Withdrawn]=0,"In Process","Withdrawn")) AS [Date Submitted to WFB],
And also the WHERE clause from the old query that is:
((([Lease Tracking].[Client Office])="East") AND (([Lease Tracking].[Date Submitted to WF]) Between [First Day of the Month] And [Last Day of the Month])) OR ((([Lease Tracking].[Client Office])="East") AND (([Lease Tracking].[Date Submitted to WF]) Is Null))
on the part that should also return records that has yet to be submitted...
I know this could be a handful, but I would appreciate anyone's help... I have been working and re-writing this query several ways and what I posted is what I think is closest to what I think the should be..
Thank you again...