[AccessD] SQL Question

Test message. No email from this morning

Martin


----- Original Message ----- 
From: "Gustav Brock" <gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Tuesday, June 24, 2003 6:25 PM
Subject: Re: [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>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>