Results 1 to 6 of 6
  1. #1
    Lorlai is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    107

    Operation must be an updatable query

    I am trying to perform an update query, but cannot run it. Whenever I try, I get an error:

    Operation must use an updateable query
    I'm not sure why this is happening, as there aren't any calculated fields that I am trying to update. My structure is below, is it possible this error is happening because I am pulling data through several different tables? I'd appreciate any advice on this!

    Product Table
    Product Number
    Product Name
    Product Time


    Product Region
    Manager
    Expected Sale Profit
    Expected Sale Tax

    The product table includes a grouped by function, so that everything is obtained through a grouped Product Number. This is a select query that saves the query table as the Product table.

    Store Table
    Product Number
    Product Status
    Product Store
    Store Number
    NWest Sales
    NEast Sales
    SWest Sales
    SEast Sales

    The Product table is joined to the Store table through the Product Number. This is a select query that pulls data from both of the tables and creates the combined table:

    Combined Table

    Product Number
    Product Name
    Product Time
    Product Region
    Manager
    Expected Sale Profit
    Expected Sale Tax
    Product Number
    Product Status
    Product Store
    Store Number
    NWest Sales
    NEast Sales
    SWest Sales
    SEast Sales

    I am trying to use the Combined query table to update the Master Product Table. Is there a better way to go about this? Or a way to prevent this error?

    Master Product Table
    Product Number
    Product Name
    Product Time
    Product Region
    Manager
    Expected Sale Profit
    Expected Sale Tax
    Product Number
    Product Status
    Product Store
    Store Number
    NWest Sales
    NEast Sales
    SWest Sales
    SEast Sales

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Post the SQL of the query, not the table fields. Go to the query in Design view and then under VIEW select SQL VIEW and copy and paste that here.

    Also, here is a list of why a query can be non-updatable:
    http://allenbrowne.com/ser-61.html

    but I'm guessing that you didn't formulate the query properly.

  3. #3
    Lorlai is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    107
    Thank you for your help, Bob. I apologize for not providing the correct amount of information.


    The 1st SQL Query pulls data from my master data sheet and creates a Product Table:

    Code:
    SELECT [Master Data].[Product Number], [Master Data].[Product Name], [Master Data].[Product Time], 
    [Master Data].[Product Region], [Master Data].[Manager], [Master Data].[Expected Sale Profit], 
    [Master Data].[Expected Sale Tax], [Master Data].[Manager Code]
    
    FROM [Master Data]
    
    GROUP BY [Master Data].[Product Number], [Master Data].[Product Name], [Master  Data].[Product Time], 
    [Master Data].[Product Region], [Master  Data].[Manager], [Master Data].[Expected Sale Profit], 
    [Master  Data].[Expected Sale Tax], [Master Data].[Manager Code]
    
    HAVING (([Master Data].[Manager Code ])="Specific Name"));
    Product Table
    Product Number
    Product Name
    Product Time
    Product Region
    Manager
    Expected Sale Profit
    Expected Sale Tax

    With the query created Product Table, I then join the Product Table with the Store Table to create the Combined Table:

    Code:
    SELECT [Product].[Product Number], [Product].[Product Name], [Product].[Product Time],
     [Product].[Product Region], [Product].[Manager], [Product].[Expected Sale Profit], 
    [Product].[Expected Sale Tax], Store.[Product Status], Store.[Product Store], 
    Store.[Store Number], Store.[NWest Sales], Store.[SWest Sales], Store.[NEast Sales], 
    Store.[SEast Sales]
    
    FROM [Product] INNER JOIN Store ON [Product].[Product Number] = Store.[Product Number];

    Combined Table

    Product Number
    Product Name
    Product Time
    Product Region
    Manager
    Expected Sale Profit
    Expected Sale Tax
    Product Status
    Product Store
    Store Number
    NWest Sales
    NEast Sales
    SWest Sales
    SEast Sales

    My update query that is not working is joining the Combined table with the Master Product table:

    Code:
    UPDATE [Master Product] 
    
    LEFT JOIN [Combined] ON [Master Product].[Product Number] = [Combined].[Product Number] 
    
    SET [Master Product].[Product Name] = [Combined].[Product Name], 
    [Master Product].Timing = [Combined].[Product Time], 
    [Master Product].[Region] = [Combined].[Product Region],
    [Master Product].[Manager Name] = [Combined].[Manager], 
    [Master Product].[Sale Profit] = [Combined].[Expected Sale Profit], 
    [Master Product].[Sale Tax] = [Combined].[Expected Sale Tax], 
    [Master Product].[Product Status] = [Combined].[Product Status], 
    [Master Product].[Store Name] = [Combined].[Product Store], 
    [Master Product].[Store Code] = [Combined].[Store Number], 
    [Master Product].[NW Sales] = [Combined].[NWest Sales], 
    [Master Product].[NE Sales] = [Combined].[NEast Sales], 
    [Master Product].[SW Sales] = [Combined].[SWest Sales], 
    [Master Product].[SE Sales] = [Combined].[SEast Sales];

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Let's step back a moment. Nowhere in any of this do you create any tables. If you did, they would be Make Table Queries and the SQL would include INSERT INTO. What you are doing is nesting Select queries and the source I'm guessing is an Excel Spreadsheet. You can't update an Excel Spreadsheet from Access (since MS lost a lawsuit regarding that many years ago).

    So when you say you are "creating a Master Table" and "creating a Combined Table" you really are not doing that. And therein lies your problem.

  5. #5
    Lorlai is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Posts
    107
    When I say creating a table, I mean that I am creating query tables (I'm not certain if there is a better name). My master data is imported into a table from an excel spreadsheet, yes, but it is a created table from the import. From the master data table, I have the Select queries that I posted. Through Access, I am able to use these select queries as a table. This has worked in the past for me for similar type projects, which is why I have done so now.


    Are you saying that what I need to do is go through the process of changing these select queries into tables? I can rework my system if this is what needs to be done.

  6. #6
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    1. An Excel spreadsheet cannot be updated through being a linked table in Access.

    2. When you create a query, you create a query - not a table. The only time you would use the term "table" is when it is an actual table. Not a query.

    3. So, anyway, you are not going to be able to update that Excel spreadsheet from Access directly as a linked table.

    4. Select queries can normally be used like a table (I say like a table because they are not a table and don't call them that - that is just some helpful advice as you learn your way around the database world). But, if it is to a linked Excel spreadsheet, they will be READ ONLY - not updatable.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 23
    Last Post: 01-24-2012, 12:46 PM
  2. Operation must use an updateable query.
    By pericherlasuma in forum Access
    Replies: 1
    Last Post: 05-16-2011, 09:45 AM
  3. Operation must use an updateable query
    By skaswani in forum Queries
    Replies: 0
    Last Post: 12-23-2010, 11:59 PM
  4. Operation Must use an Updateable Query
    By Lady_Jane in forum Queries
    Replies: 2
    Last Post: 12-14-2010, 03:02 PM
  5. Replies: 1
    Last Post: 12-12-2009, 10:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums