Re-creating a query from unnormalized to normalized table 3

[iuianj07]

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):

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:

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...

 

[lameid]

Just scanning the two queries, it looks like you have different criteria between the two.

That said I would enourage you to write a query that recreates the unnormalized result set. That should demonstrate to you and us clearly what is invovled to get to the same results.

Also while slower performing than re-writing your queries, if you name this query the same as your original table and alias the fields back to the original names, your old queries should work (some may not as they may be too complex once based on the query).

 

[iuianj07]

hello lameid,

on what part of the new query does it have a different criteria from the old query? I am very confused with this... =(

Also, how should I be able tp re-write the query that recreates the unnormalized result set if I have a way different table structure now?

thank you very much, as I am very stumped on this one

 

[dhookom]

To create an un-normalized structure, you typically use a crosstab query. I think your Column Heading would be: tblStatusChange.Status

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Criteria.... the office criteria = xxxxxx and status = 9 looks differnet.


Adding to Duane's crosstab comment.... you would join all the tables you separated out of the original table originally and possibly use a crosstab if you need to change rows to columns.

The baser questions I am getting at by suggesting writing the query is what did you do to denormalize your table, what are the relationships between your tables?

 

[iuianj07]

hello guys,

I am completely at a loss here... is it possible to provide sample SQL on how to do this?

With regards to the relationships between my tables, I think I've posted that on my first post? with what the PK and FK is for each table? If I missed something or didn't understand please let me know and advanced apologies...

and about joining all tables that i've separated before, I've used tblLoanStatus and joined it as a subquery to get the fields that the unnormalized query had?

Thank you very much for helping me out, I am literally pulling my hair out because of this...

 

[iuianj07]

I tried to create this crosstab:

TRANSFORM Max(StatusDate) AS Status_Date
SELECT tblJobTracking.SitusID
FROM tblJobTracking INNER JOIN tblLoanStatus ON tblLoanStatus.SitusID = tblJobTracking.SitusID
GROUP BY tblJobTracking.SitusID
PIVOT tblLoanStatus.[Status];

so now it returns records that looked like the un normalized table structure,, haven't tested this yet on my query above, I'll keep you guys posted...

Thank you so much

 

[iuianj07]

Hello,

So I have created this crosstab query:

ctqryStatus

TRANSFORM Max(tblLoanStatus.StatusDate) AS Status_Date
SELECT tblJobTracking.SitusID
FROM (tblJobTracking INNER JOIN tblLoanStatus ON tblJobTracking.SitusID = tblLoanStatus.SitusID) INNER JOIN tblStatusChange ON tblLoanStatus.Status = tblStatusChange.StatusChangeID
GROUP BY tblJobTracking.SitusID
PIVOT tblStatusChange.Status;

then using this query, I created another query using the crosstab query:

SELECT tblJobTracking.LoanNumber, tblJobTracking.TenantName, ctqryStatus.Received, ctqryStatus.[Analyst Issue On Hold Sent to Client], ctqryStatus.[Analyst Issue On Hold Resolved], ctqryStatus.[Submitted to Client], tblJobTracking.ClientAssetAdministrator
FROM tblJobTracking INNER JOIN ctqryStatus ON tblJobTracking.SitusID = ctqryStatus.SitusID
WHERE (((ctqryStatus.[Submitted to Client]) Between [Enter Start Date] And [Enter Last Date]) AND ((tblJobTracking.ClientOffice)=1))
ORDER BY ctqryStatus.Received;

Whenever I run this, it seems like it doesn't like to have date range criteria set into it, it gives me an error message though:

The Microsoft Office Access database engine does not recognize [Enter Start Date] as a valid name or expression.

Whever I do take off the date range criteria, the query runs correctly, although we would really need this to set a criteria...

Any idea what could have caused this?

Any help is greatly appreciated.

Thanks

 

[PHV]

You have to define the PARAMETERS in both queries:
PARAMETERS [Enter Start Date] DateTime, [Enter Last Date] DateTime;

This line should be the very first line of each query's SQL code.

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

 

[iuianj07]

hello PHV,

thanks, I included that line in the very first line of the SQL code,

Can you briefly explain to me the need of defining these parameter into the query? I have learned something new today, and it will help me understand more with your explanation.

Thank you,

 

[PHV]

An access crosstab query NEEDS this definition, don't ask me why.

 

[iuianj07]

Thanks again, so I modified the query again, and added the parameter:

PARAMETERS [Enter Start Date] DateTime, [Enter Last Date] DateTime;
SELECT tblJobTracking.LoanNumber, tblJobTracking.TenantName, ctqryStatus.Received, ctqryStatus.[Analyst Issue On Hold Sent to Client], ctqryStatus.[Analyst Issue On Hold Resolved], ctqryStatus.[Submitted to Client], tblJobTracking.ClientAssetAdministrator, ctqryStatus.Withdrawn
FROM tblJobTracking INNER JOIN ctqryStatus ON tblJobTracking.SitusID = ctqryStatus.SitusID
WHERE (((ctqryStatus.[Submitted to Client]) Between [Enter Start Date] And [Enter Last Date]) AND ((tblJobTracking.ClientOffice)=1)) OR (((ctqryStatus.[Submitted to Client]) Is Null) AND ((tblJobTracking.ClientOffice)=1))
ORDER BY ctqryStatus.Received;

I have a follow up question though, if you look at the original query from the un normalized tables, for the field Date Submitted to Client: it has this iif statement:

Date Submitted to Client: IIf(IsDate([Lease Tracking]![Date Submitted to WF]),[Date Submitted to WF],IIf([Lease Tracking]![Lease Consent Withdrawn]=0,"In Process","Withdrawn"))

I tried to copy this into the new query you helped me create, although I don't think it accepts this iff statement:

Submitted to WF: iif(IsDate[ctqryStatus]![Submitted to WF]),[ctqryStatus]![Submitted to WF],IIF([ctqryStatus]![Withdrawn] is null, "In Process","Withdrawn"))

i even tried to re-write the iif statement as:

Iif([ctqryStatus].[Submitted to WF] is not null, [ctqryStatus].[Submitted to WF], iif([ctqryStatus].[Withdrawn]is null, “In Process”,”Withdrawn”))

but still not doing it right...

can you please help me with this?

Thank you

 

[iuianj07]

I guess no one wants to answer me anymore...

anyway thanks for all of you, you've given all helpful inputs in helping me out in this query...

Thank you again...

 

[dhookom]

I guess no one wants to answer me anymore...

Getting a little impatient maybe? You do realize many of us have day jobs and don't get paid for this support.

Have you tried using Nz()?

This statement

  IIf([This Expression] Is Null, [A different Expression], [This Expression])

Can be simplified with

  Nz([This Expression], [A different Expression])

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

Hello,

I apologize, I never intended or tried to imply that I am being impatient... please never look at it that way... I am aware that most of you have day jobs too and doesn't get paid for this... I hope you realize how I appreciate every help that you and other tek tipster have given me, I am grateful that you spare a portion of your time in assisting me with all my db problems... Again, I am sorry if I sounded impatient, I was really not...

I did some research and also tried to write it with the Nz() function:

Date Submitted to WF: nz([ctqryStatus]![Submitted to Client],IIf(IsNull([ctqryStatus]![Withdrawn]),"In Process","Withdrawn"))

 

[dhookom]

Did your expression work or do you still need assistance?
Why are you still using IIf(IsNull(....)) when you can use Nz()?

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

Hello dhookom,

yes the query is working, even with the iif(IsNull(...))

I was trying to write another Nz() inside the first Nz() and seem to not make it right, so I tried to iif statement again and when it worked, I was relieved and thought of leaving it that way for now...

Thank you very much again Duane...