Microsoft Access - Syntax error on SQL Subquery

Hi Guys,

I'm trying to reduce the number of queries in my database by using subqueries.

SQL isn't my strong point, but I've managed with it so far.

This is the query I'm using;

SELECT qry1.From, qry1.To, qry1.CasesReviewed, Count(qry2.CountOfResult) AS [Cases Failed], Sum(qry2.CountOfResult) AS [Total Errors]
FROM [SELECT Min(tblChecks.DateOfCheck) AS [From], Max(tblChecks.DateOfCheck) AS [To], Count(tblChecks.CheckID) AS CasesReviewed
FROM tblEmployees INNER JOIN tblChecks ON tblEmployees.PID = tblChecks.PID
WHERE (((tblChecks.Team)='7') AND ((tblChecks.DateOfCheck)>=#6/1/2012# And (tblChecks.DateOfCheck)<=#7/3/2012#))]. AS qry1, [SELECT Min(tblChecks.DateOfCheck) AS [From], Max(tblChecks.DateOfCheck) AS [To], tblChecks.PID, tblQuestionResults.ResultID, tblChecks.CheckID, Count(tblQuestionResults.Result) AS CountOfResult
FROM tblChecks LEFT JOIN tblQuestionResults ON tblChecks.CheckID = tblQuestionResults.CheckID
WHERE (((tblChecks.Team)='7') AND ((tblQuestionResults.Result)="No") AND ((tblChecks.DateOfCheck)>=#6/1/2012# And (tblChecks.DateOfCheck)<=#7/3/2012#))
GROUP BY tblChecks.PID, tblQuestionResults.ResultID, tblChecks.CheckID]. AS qry2
GROUP BY qry1.From, qry1.To, qry1.CasesReviewed;

It seems to work until I alter anything in the WHERE clause e.g. tblChecks.Team)='7', where I get a "Invalid bracketing of name <name>. (Error 3126)" message, even if I just type over the 7 with another 7!?!

I'm looking to point the WHERE criteria to values on a form once I've figured this out.

Any idea what I'm doing wrong?

Thanks for your help

Pete Bradshaw

Sorry, I can't make sense of what you are trying to do.  Can you please explain in words what the query is trying to accomplish?

PS - Some versions of Access do not optomize subqueries well and so using them can make the queries run longer.  You will find that in the majority of cases, joins can replace the subqueries and they are much more efficient.  Adopting a good naming scheme should help you organize large numbers of queries.