SQL - Embed Query Into From Statement

[kdbailey]

Hello, I'm looking to create a recordset in which I need to embed a query into a second query.

My first query in the following code is titled "Testing".

SELECT Test.Hours, Sum([Testing].[Claims]*IIf([Confidence].[Confidence] Is Null,1,[Confidence].[Confidence])) AS Claims FROM Testing AS Test LEFT JOIN [Top 10 - Solution Confidence] AS Confidence ON (Test.User = Confidence.Username) AND (Test.[Part Assignment] = Confidence.[Part Assignment]) GROUP BY Test.Hours;

How do I replace the title of the query with the actual SQL? For instance it would be adjusted to something like the following...

SELECT Test.Hours, Sum([Testing].[Claims]*IIf([Confidence].[Confidence] Is Null,1,[Confidence].[Confidence])) AS Claims FROM 'Insert SQL that defines query Testing here' AS Test LEFT JOIN [Top 10 - Solution Confidence] AS Confidence ON (Test.User = Confidence.Username) AND (Test.[Part Assignment] = Confidence.[Part Assignment]) GROUP BY Test.Hours;

I've seen this done elsewhere but cannot find reference to it. I've tried brackets, parentheses, quotes, etc. I'm not sure how to structure it.

[June7]

Instead of

FROM Testing

try

FROM (sql string) AS Test

And if that does not work, describe what happens - error message, wrong results, nothing? And provide Testing sql statement.

[kdbailey]

I found the error.

The parentheses does the trick, however you must remove the ; from the end of the embedded sql.

SELECT Test.Hours, Sum([Testing].[Claims]*IIf([Confidence].[Confidence] Is Null,1,[Confidence].[Confidence])) AS Claims FROM (SELECT testTbl.* FROM testTbl) AS Test LEFT JOIN [Top 10 - Solution Confidence] AS Confidence ON (Test.User = Confidence.Username) AND (Test.[Part Assignment] = Confidence.[Part Assignment]) GROUP BY Test.Hours;