Hi Folks,
I have a complex query that references a sub-query. It works fine when I
call a sub-query from the main query (using design vew), but when I try to
embed the SQL text of the sub-query into the main query I get a syntax
error...
Here's the main Query. It works fine. It references another query from the
database called "Query1a"
SELECT Benchmarks.CatID, QueryA.*, Query1a.*, QueryA.Benchmarks.BenchID,
Query1a.Benchmarks.BenchID, Benchmarks.BenchID, Benchmarks.Benchmark
FROM (Benchmarks LEFT JOIN [SELECT Benchmarks.*, GroupJobs.*,
GroupJobs.GroupID, Benchmarks.CatID, Benchmarks.BenchID
FROM Benchmarks INNER JOIN GroupJobs ON Benchmarks.BenchID =
GroupJobs.BenchID
WHERE GroupJobs.GroupID=1AND Benchmarks.CatID=40]. AS QueryA ON
Benchmarks.BenchID = QueryA.Benchmarks.BenchID) LEFT JOIN Query1a ON
Benchmarks.BenchID = Query1a.Benchmarks.BenchID
WHERE (((Benchmarks.CatID)=40) AND ((QueryA.Benchmarks.BenchID) Is Not
Null)) OR (((Query1a.Benchmarks.BenchID) Is Not Null));
Here's Query1a...
SELECT AllLatestRecords.*, OrgJobs.*, Benchmarks.CatID, Benchmarks.BenchID
FROM (OrgJobs LEFT JOIN [SELECT Max(SalaryData.Dat) AS MaxOfDat,
SalaryData.BenchID, Benchmarks.CatID, SalaryData.OrgID
FROM Benchmarks INNER JOIN (OrgJobs INNER JOIN SalaryData ON OrgJobs.BenchID
= SalaryData.BenchID) ON Benchmarks.BenchID = OrgJobs.BenchID
GROUP BY SalaryData.BenchID, Benchmarks.CatID, SalaryData.OrgID
HAVING (((Benchmarks.CatID)=40) AND ((SalaryData.OrgID)=1083)) ]. AS
AllLatestRecords ON OrgJobs.BenchID = AllLatestRecords.BenchID) INNER JOIN
Benchmarks ON OrgJobs.BenchID = Benchmarks.BenchID
WHERE (((Benchmarks.CatID)=40));
I want to combine these into one single query. I have taken the entire
second query and used the following syntax...
[ text_of_Query1a_goes_here].AS Query1a
I then try to replace the Query1a in the Main query with the above syntax
....
So in the main Query, Instead of:
"...LEFT JOIN Query1a ON ...."
I Use
"...LEFT JOIN [ text_of_Query1a_goes_here].AS Query1a ON ...."
However, This gives me a syntax error (on the field name 'orgjobs')
You'll notice that the subquery 'Query1a' also references another sub query
called 'AllLatestRecords'. This one works fine when I embed it using the
same type of syntax.
I would greatly appreciate any help with this puzzle.
Best Regards,
Jack Coletti
St. Petersburg, FL