Microsoft Access

[mhartman1]

Hi,from

http://msdn.microsoft.com/en-us/library/aa217680(office.11).aspx

I have been trying to get this query to work using the Microsoft Access Northwind database:

The first SQL statement works fine but the second one gives an "Object invalid or no longer set." error.
The problem seems to be with the "Where" clause of the subquery statement.
If that is removed, It returns a ranking of 77 for each record.

Thanks in advance for any help
Mark

CODE from the article here:
----------------------------
SELECT ProductName,
SUM(Quantity) AS TotalSales
FROM [Order Details] INNER JOIN Products
ON [Order Details].ProductID =
Products.ProductID
GROUP BY ProductName
ORDER BY SUM(Quantity) DESC

After that query has been saved as qryProductTotals, you can use this query with a subquery to generate the sales rankings:

SELECT ProductName, TotalSales,
(SELECT COUNT(*)
FROM qryProductTotals AS QPT
WHERE qryProductTotals.TotalSales
<= QPT.TotalSales) AS Rank
FROM qryProductTotals
ORDER BY TotalSales DESC
----------------------------

 

[dhookom]

What are the names of all queries? You aren't attempting to save a query with the name of a query contained in its SQL statement are you?

Duane
Hook'D on Access
MS Access MVP

 

[mhartman1]

Hi Duane.

Thanks for taking the time to respond. Over the weekend, I solved the problem. The problem is that I needed to upgrade the database engine for Office 2002 on my laptop that runs Windows Vista. I went that route when I ran the same queries on my desktop computer that was running Windows XP and Office 2002 and the queries ran fine.
Thanks again.

Mark