Hi Naomi,
Ran it both ways and they give the same results - yours runs about 75% faster (4 seconds) than my original I suppose because in your way, the query created within the JOIN only executes once whereas mine executes for every row in the outer query.
Creating the "x" table as a first step also worked - very slightly faster. But I guess if you are wanting to send one string to the database to get a result set, this is better than dealing with 2 separate queries.
Albert
>I am not sure about your where clause, but I would re-write these two:
>
> ( SELECT COUNT(*) FROM Apps WHERE Apps.File_ID == PartFile.File_ID ) AS NumApps, ;
> ( SELECT MAX(Apps.LastPaymnt) FROM Apps WHERE Apps.File_ID == PartFile.File_ID ) AS LastPay_Dt ;
>
>as
>
>INNER JOIN (select count(*) as NumApps, Max(Apps.LastPaymnt) as LastPay_Dt, FIle_ID
>from Apps GROUP BY File_ID) X ON PartFile.File_ID = X.File_ID
>
>
>and would just use X.NumApps and X.LastPay_Dt in the main query
>