<!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><FONT face=Arial size=2>Hello Martin,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I am wondering whether this is a once off, or a
query that you will have to run regularly. In other words, how much effort
does it justify</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>If it is once off, you may be able to synthisise
another column that will allow the following which was copied from
BOL</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>SELECT Year, <BR> SUM(CASE
Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,<BR> SUM(CASE
Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,<BR> SUM(CASE
Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,<BR> SUM(CASE
Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4<BR>FROM
Northwind.dbo.Pivot<BR>GROUP BY Year<BR>GO</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>If the data is changing and you need it dynamic,
you could create another column, or create a dynamic column count for each
address to gain a 1,2,3,4,5 could.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Again, without knowing how much data and how much
time will be spent to solve it, it is hard to say whether it is worth creating
temp tables, populating them and then selecting the data from them. You
could do that and use an increment to help id the addresses</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Let us know more details,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I guess that if it was Jet you would use a
simple cross tab</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Mark</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=mwp.reid@qub.ac.uk href="mailto:mwp.reid@qub.ac.uk">Martin Reid</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">accessd</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Tuesday, June 24, 2003 4:32
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> [AccessD] SQL Question</DIV>
<DIV><BR></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>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE></BODY></HTML>