<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1170" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><SPAN class=761573517-24062003><FONT face="Lucida Sans Unicode"
color=#0000ff size=2>Martin,</FONT></SPAN></DIV>
<DIV><SPAN class=761573517-24062003><FONT face="Lucida Sans Unicode"
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=761573517-24062003><FONT face="Lucida Sans Unicode"
color=#0000ff size=2>Create a recordset for the resulting join and then use the
rs.Fields.Append operation on a new recordset to pull in the values from the
resulting join. Put a trap to recognize if the Staff Member name has changed and
also a trap to recognize phone1, phone2 etc... and create the new field names
for each phone number this way. When you're done write the recordset to the new
table with the data gathered.</FONT></SPAN><SPAN
class=761573517-24062003> </SPAN></DIV>
<P><FONT size=2>---<BR>Eric Barro<BR>Senior Systems Analyst<BR>Advanced Field
Services<BR>(208) 772-7060<BR><A href="http://www.afsweb.com/"
target=_blank>http://www.afsweb.com</A> </FONT></P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of </B>Martin
Reid<BR><B>Sent:</B> Tuesday, June 24, 2003 8:33 AM<BR><B>To:</B>
accessd<BR><B>Subject:</B> [AccessD] SQL Question<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2>Have two tables </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Table A Contains staff data, Name
etc</FONT></DIV>
<DIV><FONT face=Arial size=2>Table B contains contact data</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Results of join as expected</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Staff Member A Telephone Number
1</FONT></DIV>
<DIV><FONT face=Arial size=2>Staff Member A Telephone Number 2</FONT></DIV>
<DIV><FONT face=Arial size=2>Staff Member A Telephone Number 3</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>And so on</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>What I need to do is to flatten this out and
create a single table</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Staff Member A Phone Number 1
Phone Number 2 Phone Number 3
etc</FONT></DIV>
<DIV><FONT face=Arial size=2>Staff Member B
Phone Number 1 Phone Number 2</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=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.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Need this to work in Access and SQL Server
2000</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Martin</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV></BLOCKQUOTE></BODY></HTML>