> Hi again,
> In a nut shell this is what I am trying to do:
> To be a member you must take all of 14 initial classes. I am trying to
get,
> for each member, a list of the initial classes they took and what date
they
> took them. That part works fine. My problem is that if they havent taken
one
> of the classes it does not show up in the list.
> For example
> Member Name: John Doe
> Class Name                            Date Taken
> Confined Space Rescue-         1/1/2003
> Technical Rescue-                    "Not Completed"
> FirstResponder-                        4/17/02
> ....and so on.......
> The relationship set in the Accessdb is to "show all ClassID" and only
> matching TrainingDetails.
> I use one query to get the class list and then use it in my second query
to
> get the actual date of the classes for that member.
> I then dump this into an array used to fill a listview control.
> Any pointers would be appreciated.
> Thanks
> Rob

> Public Sub GetMemberInitialTraining(vlngMemID As Long, vstrClassName() As
> String, vdatTrainEndDate() As Date)
>  ReDim vstrClassName(0)
>  ReDim vdatTrainEndDate(0)

>  Dim strMemInitialTraining As String
>         Dim strInitTraining As String

>         strInitTraining = "SELECT DISTINCT tbl_Class.ClassID,
> tbl_Class.ClassName, " & _
>         "Max(tbl_Training.TrainEndDate) AS MaxOfTrainEndDate,
> tbl_Class.Initial " & _
>         "FROM tbl_Class LEFT JOIN tbl_Training ON tbl_Class.ClassID = " &
_
>         "tbl_Training.TrainingClassID GROUP BY tbl_Class.ClassID,
> tbl_Class.ClassName, " & _
>         "tbl_Class.Initial HAVING (((tbl_Class.Initial)=True))"
>         Dim recInitClasses As Recordset
>         Set recInitClasses = m_DB.OpenRecordset(strInitTraining,
> dbOpenDynaset)

>         strMemInitialTraining = "SELECT tbl_Class.ClassID, " & _
>         "tbl_Class.ClassName, tbl_Class.Initial, tbl_Class.ActiveClass, "
&
> _
>         "tbl_Training.TrainEndDate as maxoftrainenddate,
> tbl_TrainingDetails.MemberID, " & _
>         "tbl_TrainingDetails.Pass FROM (tbl_Class LEFT JOIN tbl_Training
ON
> " & _
>         "tbl_Class.ClassID = tbl_Training.TrainingClassID) LEFT JOIN " & _
>         "tbl_TrainingDetails ON tbl_Training.TrainingID = " & _
>         "tbl_TrainingDetails.TrainingID GROUP BY tbl_Class.ClassID, " & _
>         "tbl_Class.ClassName, tbl_Class.Initial, tbl_Class.ActiveClass, "
&
> _
>         "tbl_Training.TrainEndDate, tbl_TrainingDetails.MemberID, " & _
>         "tbl_TrainingDetails.Pass HAVING (((tbl_Class.Initial)=True) AND "
&
> _
>         "((tbl_Class.ActiveClass)=True) AND

> " & vlngMemID & ") " & _
>         "AND ((tbl_TrainingDetails.Pass)=True))"

>  'MsgBox strMemInitialTraining
>     Dim recMemInitTrain As Recordset
>     Set recMemInitTrain = m_DB.OpenRecordset(strMemInitialTraining,
> dbOpenDynaset)
>    ' MsgBox recMemInitTrain.RecordCount
>     Dim intCounter As Long
>     Dim intTemp As Long
>     Dim ItemToAdd As ListItem
>     intCounter = 0
> Dim strList As String

> Do While Not recMemInitTrain.EOF
>      intCounter = intCounter + 1
>     ReDim Preserve vstrClassName(intCounter - 1)
>     ReDim Preserve vdatTrainEndDate(intCounter - 1)

>     vstrClassName(intCounter - 1) = recMemInitTrain!classname
>     If Not IsNull(recMemInitTrain!maxoftrainenddate) Then

>     vdatTrainEndDate(intCounter - 1) = recMemInitTrain!maxoftrainenddate
>     strList = strList & intCounter & " " & recMemInitTrain!classname & " "
&
> recMemInitTrain!maxoftrainenddate & Chr(13)
>     Else
>     vdatTrainEndDate(intCounter - 1) = "Not Completed"
>     strList = strList & intCounter & " " & recMemInitTrain!classname & " "
&
> "Not Completed" & Chr(13)
>     End If

>     recMemInitTrain.MoveNext
> Loop
> MsgBox strList

> recMemInitTrain.Close
> recInitClasses.Close

> End Sub

> Hi,

> I had a look at the SQL statements. The first one is used to open a
> recordset, which is then closed at the end of the routine, you haven't
> actually used that recordset at all and so it is redundant (unless you use
> it in part of the code not posted).

> The second query, as you discovered, will not show empty records for the
> classes not taken, so try the following query sql string, it should give
you
> what you're after .... I think :-)

> Watch out for line wrap.

> strMemInitialTraining = "SELECT tbl_Class.ClassID, tbl_Class.ClassName, "
&
> _
> "tbl_Training.TrainingID, tbl_Training.TrainingClassID,
> Max(tbl_Training.TrainEndDate) " & _
> "AS MaxOfTrainEndDate, tbl_TrainingDetails.TrainingID,
> tbl_TrainingDetails.MemberID " & _
> "FROM (tbl_Class LEFT JOIN tbl_Training ON tbl_Class.ClassID =
> tbl_Training.TrainingClassID) " & _
> "LEFT JOIN tbl_TrainingDetails ON tbl_Training.TrainingID =
> tbl_TrainingDetails.TrainingID " & _
> "Where (((tbl_Class.Initial) = Yes) And ((tbl_Class.ActiveClass) = Yes)
And
> " & _
> "((tbl_TrainingDetails.Pass) = Yes)) Or (((tbl_Class.Initial) = Yes) And "
&
> _
> "((tbl_Class.ActiveClass) = Yes) And ((tbl_TrainingDetails.Pass) Is Null))
"
> & _
> "GROUP BY tbl_Class.ClassID, tbl_Class.ClassName, tbl_Training.TrainingID,
"
> & _
> "tbl_Training.TrainingClassID, tbl_TrainingDetails.TrainingID,
> tbl_TrainingDetails.MemberID " & _
> "HAVING (((tbl_TrainingDetails.MemberID)=" & [vlngMemID] & ")) OR
> (((tbl_TrainingDetails.MemberID) Is Null))"

> HTH

> MFK.

> > Hi again,
> > In a nut shell this is what I am trying to do:
> > To be a member you must take all of 14 initial classes. I am trying to
> get,
> > for each member, a list of the initial classes they took and what date
> they
> > took them. That part works fine. My problem is that if they havent taken
> one
> > of the classes it does not show up in the list.
> > For example
> > Member Name: John Doe
> > Class Name                            Date Taken
> > Confined Space Rescue-         1/1/2003
> > Technical Rescue-                    "Not Completed"
> > FirstResponder-                        4/17/02
> > ....and so on.......
> > The relationship set in the Accessdb is to "show all ClassID" and only
> > matching TrainingDetails.
> > I use one query to get the class list and then use it in my second query
> to
> > get the actual date of the classes for that member.
> > I then dump this into an array used to fill a listview control.
> > Any pointers would be appreciated.
> > Thanks
> > Rob

> > Public Sub GetMemberInitialTraining(vlngMemID As Long, vstrClassName()
As
> > String, vdatTrainEndDate() As Date)
> >  ReDim vstrClassName(0)
> >  ReDim vdatTrainEndDate(0)

> >  Dim strMemInitialTraining As String
> >         Dim strInitTraining As String

> >         strInitTraining = "SELECT DISTINCT tbl_Class.ClassID,
> > tbl_Class.ClassName, " & _
> >         "Max(tbl_Training.TrainEndDate) AS MaxOfTrainEndDate,
> > tbl_Class.Initial " & _
> >         "FROM tbl_Class LEFT JOIN tbl_Training ON tbl_Class.ClassID = "
&
> _
> >         "tbl_Training.TrainingClassID GROUP BY tbl_Class.ClassID,
> > tbl_Class.ClassName, " & _
> >         "tbl_Class.Initial HAVING (((tbl_Class.Initial)=True))"
> >         Dim recInitClasses As Recordset
> >         Set recInitClasses = m_DB.OpenRecordset(strInitTraining,
> > dbOpenDynaset)

> >         strMemInitialTraining = "SELECT tbl_Class.ClassID, " & _
> >         "tbl_Class.ClassName, tbl_Class.Initial, tbl_Class.ActiveClass,
"
> &
> > _
> >         "tbl_Training.TrainEndDate as maxoftrainenddate,
> > tbl_TrainingDetails.MemberID, " & _
> >         "tbl_TrainingDetails.Pass FROM (tbl_Class LEFT JOIN tbl_Training
> ON
> > " & _
> >         "tbl_Class.ClassID = tbl_Training.TrainingClassID) LEFT JOIN " &
_
> >         "tbl_TrainingDetails ON tbl_Training.TrainingID = " & _
> >         "tbl_TrainingDetails.TrainingID GROUP BY tbl_Class.ClassID, " &
_
> >         "tbl_Class.ClassName, tbl_Class.Initial, tbl_Class.ActiveClass,
"
> &
> > _
> >         "tbl_Training.TrainEndDate, tbl_TrainingDetails.MemberID, " & _
> >         "tbl_TrainingDetails.Pass HAVING (((tbl_Class.Initial)=True) AND
"
> &
> > _
> >         "((tbl_Class.ActiveClass)=True) AND
> ((tbl_TrainingDetails.MemberID)=
> > " & vlngMemID & ") " & _
> >         "AND ((tbl_TrainingDetails.Pass)=True))"

> >  'MsgBox strMemInitialTraining
> >     Dim recMemInitTrain As Recordset
> >     Set recMemInitTrain = m_DB.OpenRecordset(strMemInitialTraining,
> > dbOpenDynaset)
> >    ' MsgBox recMemInitTrain.RecordCount
> >     Dim intCounter As Long
> >     Dim intTemp As Long
> >     Dim ItemToAdd As ListItem
> >     intCounter = 0
> > Dim strList As String

> > Do While Not recMemInitTrain.EOF
> >      intCounter = intCounter + 1
> >     ReDim Preserve vstrClassName(intCounter - 1)
> >     ReDim Preserve vdatTrainEndDate(intCounter - 1)

> >     vstrClassName(intCounter - 1) = recMemInitTrain!classname
> >     If Not IsNull(recMemInitTrain!maxoftrainenddate) Then

> >     vdatTrainEndDate(intCounter - 1) = recMemInitTrain!maxoftrainenddate
> >     strList = strList & intCounter & " " & recMemInitTrain!classname & "
"
> &
> > recMemInitTrain!maxoftrainenddate & Chr(13)
> >     Else
> >     vdatTrainEndDate(intCounter - 1) = "Not Completed"
> >     strList = strList & intCounter & " " & recMemInitTrain!classname & "
"
> &
> > "Not Completed" & Chr(13)
> >     End If

> >     recMemInitTrain.MoveNext
> > Loop
> > MsgBox strList

> > recMemInitTrain.Close
> > recInitClasses.Close

> > End Sub