Pass through Query problem
Greetings All,
I'm having a problem getting the same data result from a pass through query from Access 2007 to an MS SQL database.
Please let me know if this should go into an SQL forum.
When I run the query in management studio, I get the results I expect to get.
But when I run the query from a pass-through in MS Access 2007, the results are not even close to the same.
Here is the query;
Code:
SELECT c1.order_no,
SELECT c2.lot_ser + ', ' AS [text()]
FROM dbo.lot_bin_ship c2
WHERE (c2.tran_no = c1.order_no)
FOR XML PATH('')) [SerialNum]
FROM dbo.orders c1
WHERE c1.status = 'P'
GROUP BY c1.order_no
ORDER BY c1.order_no DESC
The part in red concatenates the data in the lot.ser field on to one line for the each order number.
The concatenation works fine. I just get a different set of data.
Again, please let me know if this is the right forum to post this question.
Thanks
Greg :confused: