[AccessD] Need help

Hi Doris

Here's a variation where you save the SQL to look up the Qualifications in a separate query assuming that your primary key, VolNum, is a number:

  PARAMETERS lngKey Long;
  SELECT 
    QualificationCode
  FROM
    tblQualifications
  WHERE
    VolNum = lngKey
  ORDER BY
    QualificationCode;

Save this as, say, qdyQualifications.

Now, adjust you main query to include the function below to look something like this:

  SELECT
    *,
    ConcatenateRecords('qdyQualifications',[VolNum],'QualificationCode',', ')
  FROM
    tblVolunteers;

/gustav

<code>

Public Function ConcatenateRecords( _
  ByVal strSource As String, _
  ByVal lngKey As Long, _
  ByVal strField As String, _
  Optional ByVal strSeparator As String = ";") _
  As String

' Concatenates values from one field (strField) from all
' records in query strSource using parameter value lngKey.
' Values are separated by strSeparator.
' Default output like:
' 34;56;34;67;234
'
' 1999-10-12. Cactus Data ApS, CPH

  Dim dbs         As DAO.Database
  Dim qdf         As DAO.QueryDef
  Dim rst         As DAO.Recordset
  Dim fld         As DAO.Field
    
  Dim booPluralis As Boolean
  Dim strFields   As String
  
  On Error GoTo Err_ConcatenateRecords
  
  Set dbs = CurrentDb()
  
  If Len(strSource) > 0 And Len(strField) > 0 Then
    Set qdf = dbs.QueryDefs(strSource)
    qdf.Parameters(0) = lngKey
    Set rst = qdf.OpenRecordset()
    Set fld = rst.Fields(strField)
    
    With rst
      While Not .EOF
        If booPluralis = True Then
          ' There is more than one record.
          ' Add separator.
          strFields = strFields & strSeparator
        End If
        strFields = strFields & Trim(fld.value)
        booPluralis = True
        .MoveNext
      Wend
      .Close
    End With
    
    Set fld = Nothing
    Set rst = Nothing
    Set qdf = Nothing
  End If
  
  Set dbs = Nothing
  
  ConcatenateRecords = strFields
  
Exit_ConcatenateRecords:
  Exit Function
  
Err_ConcatenateRecords:
  MsgBox "Error " & Err.Number & ". " & Err.Description
  Resume Exit_ConcatenateRecords
  
End Function

</code>

>>> DorisH3 at aol.com 24-11-2005 03:59 >>>
I am fairly new to Access, so I apologize ahead of time for my  incompetence.
 
I have a report that I need to put together which shows the First and Last  
Names of Volunteers and their qualifications...I need to show as an  example:
 
    Last  Name            First  Name               Qualifications
     Smith                     Ann                          BB, CC, MF, 
     Jones                     Richard                     CF, CT
 
I have 2 tables....one for the volunteers which contains VolNum, FirstNm,  
LstNm,Addr,City,St,Zip and then I have a 2nd table which contains VolNum and  
QualificationCode.  Because the volunteers are qualified in more than one  area 
their could be several records for one volunteer in the Qualifications  table. 
 
 
My problem is how do I show the last column of the report with commas after  
each record from the Qualifications table vs listing each qualification on a  
seperate line for the volunteer.
 
Thanks ahead of time for the assistance.
 
Doris
-- 
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com 

This mail is marked as non spam by Pinjo revealer, spamfilter technology. ( http://www.pinjo.nl )