[AccessD] SQL Question

Martin,

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.
---
Eric Barro
Senior Systems Analyst
Advanced Field Services
(208) 772-7060
http://www.afsweb.com

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Martin Reid
  Sent: Tuesday, June 24, 2003 8:33 AM
  To: accessd
  Subject: [AccessD] SQL Question


  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

  Martin






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.489 / Virus Database: 288 - Release Date: 6/10/2003
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030624/d5544062/attachment-0001.html>