Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2006
    Posts
    6

    Red face Unanswered: MS Access UPDATE SQL with row count

    I'm trying to write what should be a simple nested UPDATE SQL statement in Access 2000 to basically do a row count. it should set the 'TotalSold' column in my 'Products' table to the count of the rows in the 'Sales' table, it goes something like...

    UPDATE Products SET TotalSold = ( SELECT COUNT(1) FROM Sales WHERE Sales.ProductID = Products.ID );

    ...but I get the error 'Operation must use an updateable query' even though I have full permission and it works fine when I replace the nested query with '1'.

    I have to use an UPDATE query to set the TotalSold column and can't use view queries or anything, any ideas?

    thanks
    Last edited by aotter; 01-05-06 at 13:51.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I hate to ask but why are you updating your products table with total sold, when it is derived from items in Sales. Its derived data

  3. #3
    Join Date
    Jan 2006
    Posts
    6

    Wink Textbook vs Practicality

    Sorry, I thought someone would ask It's for a web based database system that can only display tables and not queries. Writing a front end to display queries would be the textbook approach, but not exactly practical! If an UPDATE query exists to do it then it would make the task 100 times easier, after all I guess all outcomes of an UPDATE query are essentially derived data.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What are you using to develop your front end that doesn't support queries?

    All UPDATE queries are not derived data. Usually they are used to set an attribute given a set of parameters.

    ie:
    UPDATE myTable
    SET myStatus = 2
    WHERE myCriteria = "Something that needs to be set to 2"


    The reason it is questionable to rely on update queries to create redundant derived data is consistancy. Everytime a value changes, you must re-populate all of your derived data. There ARE applications for doing this, but they are generally relegated to data warehouse scenarios.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jan 2006
    Posts
    6
    It's an in house ASP vbscript front end, pretty massive with lots of complicated table interfunctionality and will never have to display any query so it's just not worth writting a front end to do it. Plus it's rather mission critical so in future the 'TotalSales' field will be generated by other means, offering useful trouble shooting should it disagree with the Sales table.

    If you know it's not possible could you tell me and I'll just write some VBscript to do it.

    thanks.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    ASP.NET or ASP classic? Either one most definately supports SQL query recordsets.

    Here's a reference for Classic. And here's one for .NET.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Jan 2006
    Posts
    6

    Exclamation

    Of course ASP can display a query, but the reason I asked if the UPDATE query was possible is it offers valuable mission critical trouble shooting information if the so called 'derived' field does not agree with the value it would be 'derived' from. plus it would be 100 times quicker than converting the system to display queries, yes it is that complex a system!!!

    *** please could people only post replies to the first request of a solution to the UPDATE query, if I could use SELECT to derive the data I would have. thanks. ***

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    *accidentally gave an MS SQL solution*


    Have you tried using DCount instead of a subquery?
    Last edited by Teddy; 01-05-06 at 17:34.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Jan 2006
    Posts
    6
    yeah that did the trick, great. thanks.

    pitty I couldn't get it to work with UPDATE though, if I have to code it into the system it'll come back and bite me should we ever port to another database

    thanks again

    vince

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    DCount didn't work in your UPDATE statement? it should...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •