Use alias to incorporate Query Object on Inner Join

[ItsMe]

Not sure if the thread title is correct. I have two query objects that I would like to be one query object (use a single SQL string).



The first query, qry_A_A_UnitsIn, establishes whether or not a given record in tblInvLumb has any related records in tblPieces. I include the field PurchOrdFK in the SELECT statemnt to provide a join to the second query.

SELECT tblInvLumb.PurchOrdFK, tblInvLumb.InvLumbPK, tblPieces.InWare
FROM tblInvLumb INNER JOIN tblPieces ON tblInvLumb.InvLumbPK = tblPieces.InvLumbFK
WHERE (((tblPieces.InWare)=False));

The second query uses group by to provide a count of records in tblInvLumb for a given purchase order (record in tblPurchOrd). The second query joins the first query on PurchOrdPK/PurchOrdFK.

SELECT tblPurchOrd.PurchOrdPK, Count(qry_A_A_UnitsIn.InvLumbPK) AS CountUnits
FROM tblPurchOrd INNER JOIN qry_A_A_UnitsIn ON tblPurchOrd.PurchOrdPK = qry_A_A_UnitsIn.PurchOrdFK
GROUP BY tblPurchOrd.PurchOrdPK;

I am curious if there is a way to use alias' to eliminate the need for qry_A_A_UnitsIn, as an object.

Using Is Null to find unmatched records may be another approach but I want to try to learn a little more about creating alias'.

[June7]

Should be possible. Switch second query to SQL view and copy/paste the first query.

SELECT tblPurchOrd.PurchOrdPK, Count(qry_A_A_UnitsIn.InvLumbPK) AS CountUnits
FROM tblPurchOrd INNER JOIN

(SELECT tblInvLumb.PurchOrdFK, tblInvLumb.InvLumbPK, tblPieces.InWare
FROM tblInvLumb INNER JOIN tblPieces ON tblInvLumb.InvLumbPK = tblPieces.InvLumbFK
WHERE (((tblPieces.InWare)=False))) AS qry_A_A_UnitsIn

ON tblPurchOrd.PurchOrdPK = qry_A_A_UnitsIn.PurchOrdFK
GROUP BY tblPurchOrd.PurchOrdPK;

[ItsMe]

That is definately working. My previous attempts using 'AS' failed. I will have to go back and see what I was doing wrong. Thanks for spacing out the SQL in your post.

Now I have something I can use at the form level to dynamicaly adjust Recordsets/Rowsources without stumbling over so many query objects.