Union query or combining records? 1

[tyemm]

I need help combining records to create a new table with more fields. Here's a conceptual example: I have one table with three fields:
Name Dept Facility
Joe A 1
Joe A 2
Steve B 1
Bill A 2

As there's only one Joe here, my goal is a table/query:
Name Facility(1)Dept Facility(2)Dept
Joe A A
Steve B
Bill A

etc. That is, one record per name, including everyone with at least one Facility/Dept combination, but permitting one name to have two of these.

I wish I could express this more efficiently!

Thanks,
Tom

 

[PHV]

A starting point:
SELECT U.Name, Max(U.Dept1) AS [Facility(1)Dept], Max(U.Dept2) AS [Facility(2)Dept]
FROM (
SELECT Name, Dept AS Dept1, ' ' AS Dept2 FROM theTable WHERE Facility=1
UNION ALL SELECT Name, ' ', Dept FROM theTable WHERE Facility=2
) AS U
GROUP BY U.Name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886