0

I'm just trying to concatenate the names of employees into a single field whenever the WorkOrderNumber value of an entry is the same. 

$Data = "SELECT tt.WorkOrderNumber AS WN, 
                SUBSTRING(SELECT tt2.AssignedEmp 
                FROM TestTable AS tt2 
                WHERE tt2.WorkOrderNumber=tt.WorkOrderNumber 
                ORDER BY tt2.AssignedEmp) AS emp
                FROM TestTable AS tt";

Whenever I run this query I get this returned on my site:

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] At most one record can be returned by this subquery.

Is there something I'm missing?

For clarification... I'm shooting for:

Lets say I have data in this form

WorkOrderNumber  AssignedEmp
2012087-28       Jeff      
2012087-28       Bill       
2012087-28       John

I'd like to query this data and get a result like this...

WorkOrderNumber  Employee            
2012087-28       Jeff,Bill,John
Hoser
  • 4,974
  • 9
  • 45
  • 66
  • I'm not sure if MS Access supports it but SQL Server has the `FOR XML PATH` function. See: http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server – JodyT Mar 05 '13 at 23:31
  • I tried working with that but it's been giving me syntax errors. Perhaps it doesn't support it – Hoser Mar 05 '13 at 23:43
  • Access doesn't. You definitely can't do this in Access 2003 or earlier and I'm pretty sure you cannot do this in Access at all (I was thinking maybe multivalued fields would help but it doesn't look like they will) without using VBA. – Dan Metheus Mar 05 '13 at 23:44
  • That's unfortunate. Thanks for the clarification on that – Hoser Mar 06 '13 at 00:02

1 Answers1

2

This is returning more than one record:

 SELECT tt2.AssignedEmp 
 FROM TestTable AS tt2 
 WHERE tt2.WorkOrderNumber=tt.WorkOrderNumber 
 ORDER BY tt2.AssignedEmp

which isn't going to work. You can throw a TOP (1) on it for a quick and dirty fix, but I suspect what you are expecting to be returned by this sub query isn't being returned, so fixing it this way would probably be a bad idea.

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • Do you have any insight on how that query should be written? What I'm shooting for is just a concatenation of all AssignedEmp values when the WorkOrderNumber values match – Hoser Mar 05 '13 at 23:15