How would I go about combining records?

[hzeigler4]

I have a table of all staff members. A staff member can work at more than one location. The currecnt staff table I was given contains a record for every location they work. For instance:

Name Location
Heather Office A
Heather Office B
Heather Office C
Sarah Office A
Tom Office B
Tom Office D


What I need is to to only have one record for each staff member but that record will have a column for each location. There are a possibility of 50 locations so i really didnt think I could use a crosstab.

So if I could figure out how to do this it would look like:

Name Location1 Location2 Location3 Location4
Heather Office A Office B Office C
Sarah Office A
Tom Office B Office D


Each staff member will have no more than 5 locations and no less than one location. So what I did first was created a table with the fields:

Name
Location 1
Location 2
Location 3
Location 4
Location 5

I was thinking I could then somehow append and update the locations for each staff member but this is a little beyond my expertise. Any suggestions on how I could easily do this? Help????

 

[jjturner]

Using some nested subqueries, you could obtain your results this way:

SELECT DISTINCT tMain.Name,
(SELECT TOP 1 L1.Location FROM myTable L1 WHERE
 L1.Name=tMain.Name ORDER BY L1.Location) As Location1,
(SELECT TOP 1 L2.Location FROM myTable L2 WHERE
 L2.Name=tMain.Name AND L2.Location > 
    (SELECT TOP 1 L1.Location FROM myTable L1 WHERE
     L1.Name=tMain.Name ORDER BY L1.Location)
 ORDER BY L2.Location) As Location2,
(SELECT TOP 1 L3.Location FROM myTable L3 WHERE
 L3.Name=tMain.Name AND L3.Location Not In
    (SELECT TOP 2 L1.Location FROM myTable L1 WHERE
     L1.Name=tMain.Name ORDER BY L1.Location)
 ORDER BY L3.Location) As Location3,
(SELECT TOP 1 L4.Location FROM myTable L4 WHERE
 L4.Name=tMain.Name AND L4.Location Not In
    (SELECT TOP 3 L1.Location FROM myTable L1 WHERE
     L1.Name=tMain.Name ORDER BY L1.Location)
 ORDER BY L4.Location) As Location4,
(SELECT TOP 1 L5.Location FROM myTable L5 WHERE
 L5.Name=tMain.Name AND L5.Location Not In
    (SELECT TOP 4 L1.Location FROM myTable L1 WHERE
     L1.Name=tMain.Name ORDER BY L1.Location)
 ORDER BY L5.Location) As Location5
FROM myTable tMain;

Making sure to substitute in the actual name of your table wherever you see "myTable" above.

HTH,
John