[AccessD] SQL Question

Hi Martin

> Have two tables

> Table A Contains staff data, Name etc
> Table B contains contact data

> Results of join as expected

> Staff Member A  Telephone Number 1
> Staff Member A Telephone Number 2
> Staff Member A Telephone Number 3

> And so on

> What I need to do is to flatten this out and create a single table

> Staff Member A     Phone Number 1     Phone Number 2     Phone Number 3 etc
> Staff Member B      Phone Number 1    Phone Number 2

> We dont knwo how many numbers will exist for each member of staff but the Max will be 5 and I must end up with a single table containing the data.

> Need this to work in Access and SQL Server 2000

For Access you'll have to write a function which for each Staff record
loops through the Contact records.
Here's an example of the essential part (taken from a report, thus
you'll have to modify it for writing/updating a table but you'll sure
get the picture). The ID is lngID:

<snip>

  strSQL = vbNullString
  strSQL = strSQL + "SELECT Phone "
  strSQL = strSQL + "FROM tblContacts "
  strSQL = strSQL + "WHERE (ID = " & lngID & ") "
  strSQL = strSQL + "ORDER BY Phone;"
  Set rst = dbs.OpenRecordset(strSQL)
  
  If rst.RecordCount = 0 Then
    ' Should not happen.
  Else
    ' Count records.
    lngRecords = rst.RecordCount
    rst.MoveFirst
    If lngRecords = 1 Then
      strPhone = "Telephone number: "
    Else
      strPhone = "Telephone numbers: "
    End If
    For lngLoop = 1 To lngRecords
      ' Build line with phone numbers.
      strPhone = strPhone & rst!Phone
      If lngLoop = lngRecords Then
        strPhone = strPhone & "."
      Else
        strPhone = strPhone & ", "
      End If
      rst.MoveNext
    Next lngLoop
  End If
  
  rst.Close
  Set rst = Nothing
  Set dbs = Nothing
  
  strPhoneNumbers = strPhone

</snip>