By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 1,018 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

How to combine Queries

P: n/a
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




Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.