Sorting Records in Form 1

[iuianj07]

Hello guys,

I have a main form frmJob_TrackingAll which 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
ORDER BY Job_Tracking.Date_Requested DESC;

and I have a table named Indexing_Loan_Status which store records of each phase/status for each Situs_ID

Indexing_Loan_Status

LoanStatusID -- PK
Situs_ID -- FK for Job_Tracking table
Analyst -- user
InStChID -- FK for Indexing_Status_Change table
StatusTime
StatusDate
StatusComment

Indexing_Status_Change -- lookup table for all status/phase that a Situs_ID could have

InStChID -- PK
Status


Based on the query that is bound on the frmJob_TrackingAll, it returns all records that we have, what we are trying to do now is that we would like to sort it where in when frmJob_Tracking all is opened, it is sorted that loans that have child records in Indexing_Loan_Status table that doesn't have InStChID = 10 or 16, then records that doesn't have child records yet in Indexing_Loan_Status (which means records that have not been worked on/assigned yet), then lastly records that have InStChID = 10 or 16...

Is this even possible to sort out records this way?

What I tried to do was I created a sub-form (subfrmSentToClient) for frmJob_TrackingAll which the sub-form is bound to this query:

SELECT
 Indexing_Loan_Status.Situs_ID,
 Indexing_Loan_Status.StatusDate
FROM
 Job_Tracking
LEFT JOIN
 Indexing_Loan_Status 
ON
 Job_Tracking.Situs_ID = Indexing_Loan_Status.Situs_ID
WHERE
 (((Indexing_Loan_Status.InStChID)=10
Or (Indexing_Loan_Status.InStChID)=16));

and linked this sub-form on frmJob_TrackingAll via Situs_ID...

but once I go to form view and see records that have a date on it, I however couldn't filter on it to show records that doesn't have a Sent To Client date on it...


Any help is greatly appreciated.

Thank you,

 

[MajP]

Without sitting down and trying this in sql my solution would be a function something like the following. All table and field names are guesses

public function getSort(jobID as variant) as integer
  dim strCriteria as string
  '1 - has indexing records but no 10 or 16
  '2 - has no indexing records
  '3-  has indexing 10 or 16
  dim cntIndexing as integer
  if not isNull(jobID)
   strCriteria = "jobTrackID = " & jobID 
   '
   cntIndexing = dcount ("someField","Indexing_Loan_Status" strCriteria)
   if cntIndexing = 0 then
     getSort = 2
   else
     strCriteria = strCriteria & " AND (inSTChiID = 10 or inSTChiID = 16)"
     cntIndexing = dcount ("someField","Indexing_Loan_Status" strCriteria)
     if cntIndexing = 0
        getSort = 1
     else
       getSort = 3
     end if 
   end if
end function

select ..... order by getSort([Job_Tracking].[Situs_ID])

 

[iuianj07]

MajP,

I want to be like you when I grow up...

This is the modified code, please let me know if there is something wrong here, but at first glance of frmJob_TrackingAll it looks like it is sorting correctly?

Public Function getSort(jobID As Variant) As Integer
  Dim strCriteria As String
  '1 - has indexing records but no 10 or 16
  '2 - has no indexing records
  '3-  has indexing 10 or 16
  Dim cntIndexing As Integer
  If Not IsNull(jobID) Then
   strCriteria = "Situs_ID = " & jobID
   
   cntIndexing = DCount("Situs_ID", "Indexing_Loan_Status", strCriteria)
   If cntIndexing = 0 Then
     getSort = 2
   Else
     strCriteria = strCriteria & " AND (InStChID = 10 or InStChID = 16)"
     cntIndexing = DCount("Situs_ID", "Indexing_Loan_Status", strCriteria)
     If cntIndexing = 0 Then
        getSort = 1
     Else
       getSort = 3
     End If
   End If
   End If
   
End Function

Also, if you have time, can you please explain to me line per line on what the code means and the logic/process it is going through? I keep staring and trying to understand it but couldn't... =(

BTW, Situs_ID is the PK for Job_Tracking table and FK in Indexing_Loan_Status table...


Thank you again MajP

 

[MajP]

Public Function getSort(jobID As Variant) As Integer
  Dim strCriteria As String
  '1 - has indexing records but no 10 or 16
  '2 - has no indexing records
  '3-  has indexing 10 or 16
  Dim cntIndexing As Integer
 'make sure you did not pass in a null jobID 
 If Not IsNull(jobID) Then
   strCriteria = "Situs_ID = " & jobID
   'at this point you have a string criteria: "Situs_ID = 1234"
   'Do a dcount to see the number of records in
   'the indexing loan status table where the Situs_ID = 1234
   cntIndexing = DCount("Situs_ID", "Indexing_Loan_Status", strCriteria)
   
   If cntIndexing = 0 Then
     'if no records exist then return a 2 
     getSort = 2
   Else
     'You cannot get to this point unless there was some records for the InSitu_ID
     'strCriteria at this point is: "Situs_ID = 1234"
    
     strCriteria = strCriteria & " AND (InStChID = 10 or InStChID = 16)"
     
    'at this point strCriteris is: 
    '"Situs_ID = 1234 AND (InStChID = 10 or InStChID = 16)"
     'Now do another dcount to see how many records exist with         
    'Situs_ID = 1234 and the (InStChiID = 10 or the InStChiD = 16)
     
     cntIndexing = DCount("Situs_ID", "Indexing_Loan_Status", strCriteria)
     If cntIndexing = 0 Then
        'So here is the case where there are records, but none that are 10 or 16
        'Therefore return 1
        getSort = 1
     Else
       'Here is the case where there are records, 
       'and some records that are 10 or 16. Return 3
       getSort = 3
     End If
   End If
   End If
End Function