CODEWITHSUNDEEP
sqlms-access
Hoser
Hoser

Reputation: 5044

Can't figure out why this query isn't running properly

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

Upvotes: 0

Views: 67

Answers (1)

Abe Miessler
Abe Miessler

Reputation: 85126

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.

Upvotes: 2

Related Questions