[AccessD] SQL Question

HI Mark

5500 employee records with up to 5 related records in the other table.

Its a one of. We need the flat file becasue of the way we will be reading the 
data into the windows security system. That bit I dont fully understand and I 
dont have to, My job is to give the security guys the data the way they want 
it. Its impossible to do what they want to do with related tables. It has to be 
flat. They can only work with a single record and that record must be complete 
for each staff member with absolutly no duplication. PITA!

Once this is done we will then look at scripting changes etc to the files.

My main system at home is trash so I cant work on it til tomorrow. 


Martin

Quoting "Mark L. Breen" <subs at solution-providers.ie>:

> Hello Martin,
> 
> 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
> 
> If it is once off, you may be able to synthisise another column that
> will allow the following which was copied from BOL
> 
> SELECT Year, 
>     SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
>     SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
>     SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
>     SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
> FROM Northwind.dbo.Pivot
> GROUP BY Year
> GO
> 
> 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.
> 
> 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
> 
> Let us know more details,
> 
> I guess that if it was Jet you would use a  simple cross tab
> 
> Mark
> 
> 
>   ----- Original Message ----- 
>   From: Martin Reid 
>   To: accessd 
>   Sent: Tuesday, June 24, 2003 4:32 PM
>   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
> 
> 
> 
> 
> 
> 
> 
> 
> ------------------------------------------------------------------------------
> 
> 
>   _______________________________________________
>   AccessD mailing list
>   AccessD at databaseadvisors.com
>   http://databaseadvisors.com/mailman/listinfo/accessd
>   Website: http://www.databaseadvisors.com
>