Two Subqueries in one statement

[casey]

How many subqueries can I put into one SQL statement? I'm trying to run a query that has two subqueries, but it's not working. It works when I create and reference one of the queries separately, but when I put them directly into the statement as subqueries, it gives me a syntax error. It would really save loads of time to have this in one statement. Can someone tell me what I'm doing wrong with this query or tell me if this is possible to use the AS identifier more than once in a query?

qryTwo: WORKS!
SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntityDetail.fldValue, tblEntityDetail.fldDate
FROM tblEntityDetail INNER JOIN [SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, Max(tblEntityDetail.fldDate) AS MaxOffldDate, tblEntity.fldInactive, tblEntity.fldTypeID
FROM tblEntity LEFT JOIN tblEntityDetail ON tblEntity.fldEntityID = tblEntityDetail.fldEntityID
GROUP BY tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntity.fldInactive, tblEntity.fldTypeID
HAVING (((tblEntity.fldInactive)=False) AND ((tblEntity.fldTypeID)=2))]. AS qryOne ON (tblEntityDetail.fldDate = qryOne.MaxOffldDate) AND (tblEntityDetail.fldDetailID = qryOne.fldDetailID) AND (tblEntityDetail.fldEntityID = qryOne.fldEntityID);

qryThree with separate reference to qryTwo: WORKS!
SELECT qryTwo.fldEntityID
FROM tblDetail INNER JOIN qryTwo ON tblDetail.fldDetailID = qryTwo.fldDetailID
GROUP BY qryTwo.fldEntityID

qryThree without separate reference to qryTwo: DOESN'T WORK!
SELECT qryTwo.fldEntityID
FROM tblDetail INNER JOIN [SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntityDetail.fldValue, tblEntityDetail.fldDate
FROM tblEntityDetail INNER JOIN [SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, Max(tblEntityDetail.fldDate) AS MaxOffldDate, tblEntity.fldInactive, tblEntity.fldTypeID
FROM tblEntity LEFT JOIN tblEntityDetail ON tblEntity.fldEntityID = tblEntityDetail.fldEntityID
GROUP BY tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntity.fldInactive, tblEntity.fldTypeID
HAVING (((tblEntity.fldInactive)=False) AND ((tblEntity.fldTypeID)=2))]. AS qryOne ON (tblEntityDetail.fldDate = qryOne.MaxOffldDate) AND (tblEntityDetail.fldDetailID = qryOne.fldDetailID) AND (tblEntityDetail.fldEntityID = qryOne.fldEntityID)]. AS qryTwo ON tblDetail.fldDetailID = qryTwo.fldDetailID
GROUP BY qryTwo.fldEntityID

Hopefully I am wording this correctly??? If anyone can help me figure this out, I would greatly appreciate it. Thanks!!

 

[mresann]

SELECT qryTwo.fldEntityID
FROM tblDetail INNER JOIN [SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntityDetail.fldValue, tblEntityDetail.fldDate
FROM tblEntityDetail INNER JOIN [SELECT tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, Max(tblEntityDetail.fldDate) AS MaxOffldDate, tblEntity.fldInactive, tblEntity.fldTypeID
FROM tblEntity LEFT JOIN tblEntityDetail ON tblEntity.fldEntityID = tblEntityDetail.fldEntityID
GROUP BY tblEntityDetail.fldEntityID, tblEntityDetail.fldDetailID, tblEntity.fldInactive, tblEntity.fldTypeID
HAVING (((tblEntity.fldInactive)=False) AND ((tblEntity.fldTypeID)=2))]. AS qryOne ON (tblEntityDetail.fldDate = qryOne.MaxOffldDate) AND (tblEntityDetail.fldDetailID = qryOne.fldDetailID) AND (tblEntityDetail.fldEntityID = qryOne.fldEntityID)] AS qryTwo  ON tblDetail.fldDetailID = qryTwo.fldDetailID
GROUP BY qryTwo.fldEntityID

See if this works. I removed a period from your original query that may have caused your syntax error (located right before "AS qryTWO")

 

[casey]

mresann,

Thanks for your reply, but I still get the error. The syntax is unfamiliar to me while the (.AS qryOne) does have the period and it works properly and that's why I put it in (.AS qryTwo), but I really don't know what it's for.

Any other ideas?

 

[mresann]

If you get a syntax error, you should be able to identify the place the error is happening. Try this:

1. In SQL view, try to open the query.
2. When the error occurs, click "OK"
3. Look at the query closely. Do you see anyplace on the query that is highlighted?

Without reconstructing all your tables and fields from scratch, I can't see anything wrong with your present query structure. The only other thing I can think of is to encase the entire sql statement inside of parantheses before transfering it to the main query.

 

[casey]

Success!!!

I removed the second period and put the entire thing in parantheses as you suggested and it worked. After working on this query for over a week, now I can continue my project . That's going to help me out a ton! Thanks a million.

 

[mresann]

After working on this query for over a week, now I can continue my project .

5 days/week x 8 hours/day x 50$/hour =

kaching!

1 pint of beer

 

[casey]

5 days/week x 8 hours/day x 50$/hour =

kaching!

Unfortunately for me, my weeks aren't as short and my time's not as valuable.

I'm getting back into database development after a long hiatus. Things are going as slowly as I remember them. Actually, I've been working on this query for about two weeks. A week to figure out the query and another week to figure out the parentheses part. Brother! Don't let my boss know!

Thanks again.