SQL Nightmare

[amacfarl]

Hi,

Once again SQL is driving me up the wall. I know this is not an SQL forum, but this is my last resort to get this App to work.

 

Scenario: Two tables, (MS ACCESS)

T_Sellers - Listing all the seller information

T_Sales - Listing all sales by product.

 

I am wanting to update a field in T_Sellers which is the total number of sales for that seller (NumOfSales). I have written this update query:

 

UPDATE T_Sellers SL

SET NumOfSales = (SELECT Count(*)

FROM T_Sales SA

WHERE SA.Seller = SL.ROW_ID)

 

I am getting the following error message:

"Operation must use an updateable query"

 

I dont understand what is happening... this should work!

 

Thanks in advance for your help!!

 

Regards

Angus

[Heiko]

First glance: The outer where clause is missing.

[Nerseus]

I know in SQL Server you can't alias a table in the UPDATE clause. But you'll get a different error than what you've said. Otherwise, the query looks fine to me (in SQL Server - never tried a subquery in Access before).

 

-Nerseus