Please Assist with Query Problem in Access

Hello All,

This is my first post here and I'm hoping I can find a good answer
to this seemingly tricky problem:

Here are some examples of queries that return the desired info.

----------------------------------------------------------------------

SELECT Companies.CompanyID, Companies.Name,
Companies.Symbol, Quotes.Price
FROM Companies
LEFT JOIN Quotes ON Companies.CompanyID =
Quotes.CompanyID
WHERE Quotes.QuoteID =
(SELECT TOP 1 Quotes.QuoteID From Quotes WHERE
Quotes.CompanyID = Companies.CompanyID) ORDER BY Quotes.QuoteID DESC
OR
Quotes.QuoteID NOT IN
(SELECT Quotes.QuoteID From Quotes)
----------------------------------------------------------------------

SELECT Companies.CompanyID, Companies.Name,
Companies.Symbol, Quotes.Price
FROM Companies
LEFT JOIN Quotes ON Companies.CompanyID =
Quotes.CompanyID
WHERE Quotes.QuoteID =
(SELECT TOP 1 Quotes.QuoteID From Quotes WHERE
Quotes.CompanyID = Companies.CompanyID ORDER BY Quotes.QuoteID DESC)
OR
Quotes.QuoteID NOT IN
(SELECT Quotes.QuoteID From Quotes)

-----------------------------------------------------------------------
These queries take absolutely forever to execute (forever = >3 minutes)
despite attemps to optimize indexes and default sort order and using only
a small test database with < 10K records. The query needs to run nearly as
fast as this one does:

SELECT Companies.CompanyID, Companies.Name,
Companies.Symbol, Quotes.Price
FROM Companies
LEFT JOIN Quotes ON Companies.CompanyID =
Quotes.CompanyID
WHERE Quotes.QuoteID =
(SELECT TOP 1 Quotes.QuoteID From Quotes WHERE
Quotes.CompanyID = Companies.CompanyID)
OR
Quotes.QuoteID NOT IN
(SELECT Quotes.QuoteID From Quotes)

------------------------------------------------------

Which executes in less than 1 second.


Thanks for any ideas,

JimH

Well if anyone is interested:

I found a query that does work:

SELECT [companies].[companyid], [companies].[name], [companies].[symbol], [quotes].[price]
FROM (companies LEFT JOIN [select max(quoteid) as maxofquoteid, companyid from quotes group by companyid]. AS q1 ON [companies].[companyid]=q1.companyid) LEFT JOIN quotes ON q1.maxofquoteid=[quotes].[quoteid];


However this query only works from within Access. I need to execute this query from inside my vb app using an ADO connection obejct. The syntax is often a little different when using ADO. The question now is can anyone translate into ADO SQL?

Store the query in Access, and call it from your VB - it's the same as calling a Stored Procedure.