More subqueries

Here's another cool subquery. The use of a subquery here allows a left join on a calculated field!

SELECT BatchID, NewSKUString, CleanedSKUNumber, SKUID, PROD_NBR, OFFRNG_ENG_DESC, CORP_STAT_CD
FROM
  [SELECT BatchID, NewSKUString, CleanSKUNumber(nz(NewSKUString),True) AS CleanedSKUNumber FROM tblNewSKULists]. AS NewSKUs
  LEFT JOIN tblExistingSKUData
  ON NewSKUs.CleanedSKUNumber = tblExistingSKUData.PROD_NBR;

(CleanSKUNumber is a function)

I read something on UtterAccess that said that when you use square brackets as above Access creates a separate query "behind the scenes". I don't know if that's true or what difference it makes. I can tell you that the above works just the same with round brackets:

SELECT BatchID, NewSKUString, CleanedSKUNumber, SKUID, PROD_NBR, OFFRNG_ENG_DESC, CORP_STAT_CD
FROM
  (SELECT BatchID, NewSKUString, CleanSKUNumber(nz(NewSKUString),True) AS CleanedSKUNumber 
   FROM tblNewSKULists) AS NewSKUs
  LEFT JOIN tblExistingSKUData 
  ON NewSKUs.CleanedSKUNumber = tblExistingSKUData.PROD_NBR;

I was also fascinated to see that if you key the above into the SQL view and then switch back to the QBE grid it shows the subquery as it were a sepeartely saved query.