Tableadapter Wizard errors with subqueries

[MajP]

In the table adapter wizard I try to do a simple sub query. I am connected to a Access database. I believe the query syntax is fine, because this works fine when run/tested in Access.

SELECT 
 P.ID, 
 P.[Product Code], 
 P.[Product Name], 
 P.[Reorder Level], 
 P.[Target Level], 
 P.[Quantity Per Unit], 
 (SELECT SUM(A.quantity)
  FROM [Inventory Transactions] AS A
  WHERE A.[Product ID] = P.ID AND A.[Transaction Type] = 3) 
  AS 
   OnHold
FROM 
 Products AS P;

In the table adapter wizard I get

Generated Select Statement
Error in Select clause: expression near 'select'
Error in select clause: expression near 'From'
Missing From clause
unable to parse text

I tried other sub queries and seem to get the same error. Is there some special syntax for subqueries when working in the ds designer? I did not try to actually run this in code, and was wondering if this was simply a "caution". I tried to Google this error and seems somewhat common, but I did not find a solution.

 

[jmcilhinney]

You should rewrite your query as a join between the Products table and that subquery.