Get Stage Date and Description Using SQL Problem

[matomo]

Hi all,

I hope someone can point me in the right direction on what I am doing wrong here. This my first dable into puting SQL into a function module and I can only get a partial result.

What I am trying to accomplish is to extract dates and descriptions from Oracle tables which I have already linked into using ODBC connection.

The problem:

The Oracle tables have what I will called Parent and Child Relationships. What I want to accomplish is to be able to extract last Dates and last Descriptions from the Parent Table and also from Child Table if a Child Table is present, but if there is no Child Table to return only Last date and Last Description from the Parent Table.

When I ran the SQL module below I can only extract the Parent Table information and not Child Table Information. The first part of my function gets the Stage Date and the second Function gets the Stage Description.

What I want to accomplish to be able to get the Stage Dates and Stage Descriptions from both the the Parent table and also the Child table (Folder) anytime a Child Table is present.

Function GetStageDate(aFolderRSN As Long) As String
Dim dbs As Database
Dim rstDate As Recordset
Dim strSQLDate As String

Set dbs = CurrentDb

strSQLDate = "SELECT Max(st.STAGEDATE)"
strSQLDate = strSQLDate & " FROM IBMS_STAGES AS st"
strSQLDate = strSQLDate & " GROUP BY st.FOLDERRSN"
strSQLDate = strSQLDate & " HAVING (((st.FOLDERRSN) = " & aFolderRSN & ") And ((Max(st.STAGEDATE)) Is Not

Null))"

Set rstDate = dbs.OpenRecordset(strSQLDate, dbOpenDynaset)

If rstDate.RecordCount > 0 Then
GetStageDate = rstDate.Fields(0)
Else
GetStageDate = ""
End If

End Function

'___________________________________________________________________________________________

Function GetStageDescription(aFolderRSN As Long)
Dim dbs As Database
Dim rst, rst2 As Recordset
Dim mrst As Recordset


Dim strStageDate As String
Dim strSQL, strSQL2, strSQL3 As String
Dim aParentRSN As Long

Set dbs = CurrentDb

strStageDate = GetStageDate(aFolderRSN)
If strStageDate <> "" Then

strSQL = "SELECT IBMS_VALIDSTAGES.STAGEDESC"
strSQL = strSQL & " FROM IBMS_VALIDSTAGES INNER JOIN IBMS_STAGES ON IBMS_VALIDSTAGES.STAGECODE =

IBMS_STAGES.STAGECODE"
strSQL = strSQL & " WHERE (((IBMS_STAGES.FOLDERRSN) = " & aFolderRSN & ") And ((IBMS_STAGES.STAGEDATE) = #"

& strStageDate & "#))"

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

If rst.RecordCount > 0 Then
GetStage = rst.Fields(0)
Else

'Check Parent Folder

strSQL2 = "SELECT parentrsn from ibms_folder where folderrsn = " & aFolderRSN & " AND parentrsn is

not null"
Set mrst = dbs.OpenRecordset(strSQL2, dbOpenDynaset)

If mrst.RecordCount > 0 Then
aParentRSN = mrst.Fields(0)

strStageDate = GetStageDate(aParentRSN)

If strStageDate <> "" Then
strSQL = "SELECT IBMS_VALIDSTAGES.STAGEDESC"
strSQL = strSQL & " FROM IBMS_VALIDSTAGES INNER JOIN IBMS_STAGES ON

IBMS_VALIDSTAGES.STAGECODE = IBMS_STAGES.STAGECODE"
strSQL = strSQL & " WHERE (((IBMS_STAGES.FOLDERRSN) = " & aParentRSN & ") And

((IBMS_STAGES.STAGEDATE) = #" & strStageDate & "#))"

End If

Set rst2 = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst2.RecordCount > 0 Then
GetStage = rst2.Fields(0)
Else
GetStage = ""
End If
Else
GetStage = ""
End If
End If

End If

End Function

What am I doing wrong with the above functions that I can not extract both Parent and Child Information from both tables (Folder).

Attached is a zipped file of the above functions.

Thanks

matomo

 

[antomack]

Without seeing some sample data I'm not positive, but you should be able to get the required data using an outer join query giving all parent records regardless of child records, i.e. left outer join from parent to child. This should give you data for all parent records that have child records and also give a record for all parents without a child but null child values.

 

[matomo]

Thanks AntoMark.

I will try your suggestion.

matomo