Correllated Subquery, unresolving outer references

[lordofhyphens]

I'm trying to form a correlated subquery (right now, it's MS Access
running to linked tables in MS SQL, although I have tried to make the
same query against the MS SQL tables directly).

The goal in the query below is to filter out near-duplicates.

What I have is similar rows in my database, and the differences between
the different rows lies in a date field. I want to be able to filter
out the "newest" dates

What happens is that Access treats all of the references in the
subquery as variables to be filled (and so prompts me), but if I use
the full table name, it defeats the purpose of a corellated subquery
(namely, to be able to use an outer reference).

SELECT dbo_ace_sites.longName, dbo_ace_taskCore.onsiteDate,
dbo_ace_taskCore.status
FROM dbo_ace_taskCore AS A1, dbo_ace_sites AS A2
WHERE (((A1.site)=[A2].[shortCode]) AND [A1].[onsiteDate] Between
DateValue("1/1/2006") and DateValue("7/1/2006") AND
(([dbo_ace_taskCore].[onsiteDate]) In (select Max(A1.onsiteDate)
FROM dbo_ace_taskCore A3 WHERE A3.shortCode = A1.shortCode )));

I'd love either a explanation of what's going wrong here, or how to fix
it, or a link explaining exactly how to write outer references with MS
SQL/Access (and the guide at
http://www.aspfree.com/c/a/MS-SQL-Se...Expressions/2/
isn't the proper syntax).

Thanks