By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,655 Members | 1,860 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,655 IT Pros & Developers. It's quick & easy.

Correllated Subquery, unresolving outer references

P: n/a
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

Aug 3 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.