Microsoft Query in Excel - nested subquery

[lespaul]

So I am trying to get the following query to run in Microsoft Query so I can return the results into a worksheet.

select  b.caspre, b.casnum, D.DEFNAM, D.DEFSSN, D.DEFADD, D.DEFCTY,
D.DEFSTT, D.DEFZCD, D.DEFDTOB, D.DPHON1, D.DEFSEQ, D.DEFRAC, D.DHAIRC,
D.DEYECO, D.DHGTFT, D.DHGTIN, D.BNDTYP, BNDAMT, BTYPE, BWNBR,
D.JUDCOD, D.STATSDAT from cmlbwpdj b
inner join ([COLOR=blue]select caspre, casnum, max(swardt) as swardt from
   cmlib/cmlbwpdj where
   caspre in('DV', 'DW') AND
   CHGSEQ=1 AND
   STSCOD=8 AND
   SWARDT BETWEEN 20070101 AND 20090910 AND
   CHGABV IN('CONTEMP', 'WARRANT') group by caspre, casnum[/color]) b1 on b.caspre = b1.caspre and b.casnum = b1.casnum and b.swardt = b1.swardt
inner join cmpdefmf d on b.caspre = d.caspre and b.casnum = d.casnum
where defcty like 'ALB%' and defdtob < 19920101

My first issue was with the alais in the nested subquery (blue), however I resolved that issue by adding the AllowAlias in the Registry, but it still won't run the query. I CAN get the inner subquery to run by itself in Microsoft Query, but not the whole thing. When I try to add the whole query I get the error:
[tt]
'Could not add table CMLBWPDJ'[/tt]

I can run this query in another query application and it works just fine....any suggestions on why it can't add the table?

Thanks!

Leslie

 

[dhookom]

Do you need to place []s around cmlib/cmlbwpdj?

Duane
Hook'D on Access
MS Access MVP