Filter Text/date From A Sub-form Help 2

[iuianj07]

Hello guys,

First of all, I would like to thank you for the time in reading my post, as it could be rather lenghty... And this is also my first time creating a properly normalized table. I have been creating Access databases before, but were not properly normalized. I have been creating spreadsheet like tables/databases before instead of a relational database tables etc...

I have a question with regards on how I will be able to filter date/text through a sub-form? This situation is new to me since before I have been using a spreadsheet style method which makes it easier to filter.

Basically what this database/our project does is that, we have a client (a Bank) wherein we index loan files for them, and sometimes create ASR (Asset Summary Report) for certain loans. They will send us notifications that they want a loan indexed, and we use this database to track our statuses for the loans we index for them. There will be different phases a loan will go through from receiving a new request up until we return the file to the client (documents received, indexing in process, indexing complete, indexing QC (quality control) in process, indexing QC complete etc...) which we put date fields, so we now on what day was this work being done.

My question now is, since we have multiple users in the database, I would like to be able to have a button on my form (Draft Form) that filters for a loan that has been worked on by the user who logged in from the login form AND loans that hasn't been worked on yet... So in example, if I logged in the database as Samuel Adams, then when I open the Draft Form, the loans that would appear on my queue are loans that I have worked on (put my name in a status -- like indexing in process etc...) And loans that hasn't been worked by anyone yet.

My Main form is bound to this query

SELECT Job_Tracking.Situs_ID, Job_Tracking.Project, Job_Tracking.Facility_Number, Job_Tracking.Requested_By, Job_Tracking.Client_Sponsor, Job_Tracking.Property_Loan_Name, Job_Tracking.PortfolioID, Job_Tracking.Date_Requested, Job_Tracking.FileSourceID, Job_Tracking.Specific_File_Instructions, Job_Tracking.File_Completion_Requested_Date, Job_Tracking.Date_Situs_Received_File, Job_Tracking.Situs_Est_Date_Of_Completion, Job_Tracking.PriorityID, Job_Tracking.Link_To_Completed_Files
FROM Job_Tracking;

Then my subform which is linked to the main form via Situs_ID is bound to Indexing_Loan_Status Table. The table structure is:

LoanStatusID - PK
Situs_ID - FK to Job_Tracking
Analyst
InStChID - FK to Indexing_Status_Change
StatusTime
StatusDate
StatusComment

Indexing_Status_Change table structure is:

InStChID - PK
Status - (which lists all of the different status the loan could go through)


Any help is greatly appreciated and please let me know if I need to give more information.

Thank you,

 

[iuianj07]

Thank you both..

 

[iuianj07]

Hello,

Just got back from a meeting about this database. They suddenly changed/added the Business Rule in this database:

For a quick scope of how the business rule changed:

We receive a loan (record) that we enter in the frmNew_Transaction, and that loan will be assigned to a user who will Index the loan (using a different software), then this loan will be reviewed (Indexing QC) then it'll be sent back to the Client (Indexing Sent to Client). Then once the client (who we do our work for) determines that additional work needs to get done, (most of the time Redaction) then they will inform us, and that's the time when we need to add another status on that loan for "Redaction Request Received" and should show up again on the frm_JobTracking.

With the new business rule change in mind. They now want to have 2 frmJob_Tracking wherein:

1.) With the same way we are returning a set of records that have related child records in at least one of two tables for a given user, or have no child records, they also want it to only return records from the Indexing_Loan_Status table that doesn't have the Status "Indexing Sent to Client" And/Or "Redaction Sent to Client". So whenever a user added that status into a record, then it shouldn't show up anymore in this form once the form is reloaded.

2.) The form will show all records, this form is used by the Project Coordinator, wherein if an existing record that is already "Indexing Sent to Client" need to have additional work done, meaning "Redaction Request Received" status has to be added.Then the Project Coordinator will add that status then this record should show up on the form on 1.)


In case you need to look at Indexing_Status_Change table:

InStChID Status
1 Indexing Request Received
2 Documents Received
3 Downloading in Process
4 Imaging in Process
5 Imaging Complete
6 Indexing in Process
7 Indexing Complete
8 Indexing QC in Process
9 Indexing QC Complete
10 Indexing Sent to Client
11 Redaction Request Received
12 Redaction in Process
13 Redaction Complete
14 Redaction QC in Process
15 Redaction QC Complete
16 Redaction Sent to Client
19 On Hold


Does this mean that a major revamp should be done to frmJob_Tracking?

Any help is greatly appreciated again, and thank you very much for the patience.

Thank you,

 

[iuianj07]

I tried to change the query

qryLoggedIndexingIDs from:

SELECT DISTINCT Job_Tracking.Situs_ID, Indexing_Loan_Status.Analyst, Indexing_Loan_Status.InStChID
FROM Job_Tracking LEFT JOIN Indexing_Loan_Status ON Job_Tracking.Situs_ID = Indexing_Loan_Status.Situs_ID
WHERE (((Indexing_Loan_Status.Analyst)=getuserName()));

which gives the result of

Situs_ID Analyst IntStChID
298 user 1
298 user 10
298 user 11
303 user 1

to:

SELECT DISTINCT Job_Tracking.Situs_ID, Indexing_Loan_Status.Analyst, Indexing_Loan_Status.InStChID
FROM Job_Tracking LEFT JOIN Indexing_Loan_Status ON Job_Tracking.Situs_ID = Indexing_Loan_Status.Situs_ID
WHERE (((Indexing_Loan_Status.Analyst)=getuserName()) AND ((Indexing_Loan_Status.InStChID)<>10));

which gives the result of

Situs_ID Analyst InStChID
298 user 1
298 user 11
303 user 1

although what I was trying to accomplish based on my previous post, is that if a Situs_ID has an InStChID of 10 (which is Indexing Sent to Client) in the Indexing_Loan_Status table, then the whole Situs_ID of that record shouldn't show up anymore, so basically the result should be:

Situs_ID Analyst InStChID
303 user 1


Any help is greatly appreciated....

 

[PHV]

What about this ?

SELECT Situs_ID, Analyst, InStChID
FROM Indexing_Loan_Status
WHERE Analyst=getuserName()
AND Situs_ID NOT IN (SELECT Situs_ID FROM Indexing_Loan_Status WHERE InStChID=10)

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

 

[iuianj07]

Thank you, it gave the right result:

but why is it that when I run the union query:

SELECT 
  qryLoggedASRIDs.Situs_ID
FROM 
 qryLoggedASRIDs
UNION SELECT
 qryLoggedIndexingIDs.Situs_ID
FROM
 qryLoggedIndexingIDs;

it still returns Situs_ID 298? Am I missing something?

Thank you

 

[PHV]

What is the SQL code of qryLoggedASRIDs ?

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

 

[iuianj07]

Hello,

the SQL is

SELECT DISTINCT Job_Tracking.Situs_ID
FROM Job_Tracking LEFT JOIN ASR_Loan_Status ON Job_Tracking.Situs_ID=ASR_Loan_Status.Situs_ID
WHERE (((ASR_Loan_Status.Analyst)=getuserName()));

thank you again

 

[PHV]

So, you wanted this ?

SELECT Situs_ID
FROM ASR_Loan_Status
WHERE Analyst=getuserName()
AND Situs_ID NOT IN (SELECT Situs_ID FROM Indexing_Loan_Status WHERE InStChID=10)

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

 

[iuianj07]

Thanks PHV, this worked correct.

I have a follow up question though...

so now it gives me only

Situs_ID
303

But when I add a new record in the Indexing_Loan_Status table in Situs_ID 298 with InStChID = 11, then it should show up again on the query...

Then, after it has been added to the query, when another record is added again with InStChID = 16 then it shouldn't show up again...

I guess I could figure out the InStChID = 16 part, but how about when it's InStChID = 11?

Thank you for your help

 

[iuianj07]

I am pulling all my hair out

 

[iuianj07]

I tried to change the query

qryLoggedIndexingIDs from:

SELECT T1.Situs_ID, T2.Analyst, T2.InStChID
FROM Job_Tracking AS T1 LEFT JOIN Indexing_Loan_Status AS T2 ON T1.Situs_ID=T2.Situs_ID
WHERE T1.Situs_ID Not In (SELECT DISTINCT T3.Situs_ID FROM Indexing_Loan_Status As T3 WHERE T3.InStChID = 10) And T2.Analyst = getuserName();

to:

SELECT T1.Situs_ID, T2.Analyst, T2.InStChID
FROM Job_Tracking AS T1 LEFT JOIN Indexing_Loan_Status AS T2 ON T1.Situs_ID = T2.Situs_ID
WHERE (((T1.Situs_ID) Not In (SELECT DISTINCT T3.Situs_ID FROM Indexing_Loan_Status As T3 WHERE T3.InStChID = 10 Or T3.InStChID = 16) Or (T1.Situs_ID) In (SELECT DISTINCT T3.Situs_ID FROM Indexing_Loan_Status As T3 WHERE T3.InStChID = 11)) AND ((T2.Analyst)=getuserName()));

and what this does now, if a Situs_ID has an InStChID = 11, it will show up in a query, but if it doesn't have InStChID = 11 but there is InStChID = 10, it now doesn't show up in the query... which is correct...

Although, If there is InStChID = 11, and I added another record with InStChID = 16, it still shows up in the query, but what I want it to do is if there's InStChID = 16, then it shouldn't show up anymore...

Any help is greatly appreciated...

thank you