Why is query not updateable

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Why is my query not updateable? Ask Question. Asked 7 years, 4 months ago. Active 3 years, 2 months ago. Viewed 25k times. Basically, the underlying tables look like this: Accounts accountId accountName accountHolder TransactionStatements statementId accountId received month year The user wants a Query view that looks like this: StatementView accountName accountHolder year janReceived febReceived marReceived etc WHERE ts1.

Any suggestions? Improve this question. Johnny Bones 8, 6 6 gold badges 40 40 silver badges bronze badges. MgSam MgSam Sadly, it's all in Access. Check for any of the instances mentioned in my answer. Pay particular attention to the relationship table and make sure there are no Many to One to Many joins — Johnny Bones. Thanks, but I've seen this in the article I linked to in my question.

I was hoping someone knew some hack to make the query updatable. Add a comment. Active Oldest Votes. Take a look at this very thorough list of reasons why recordsets are and aren't updateable: When Recordsets Are Always Updateable A recordset is always updateable when: It is based on a single table.

It is based on a query based on a single table. It is based on a query based on tables with a one-to-one relationship. It is based on a Union Query. It is an Aggregate Query that calculates a sum, average, count or other type of total on the values in a field. It is an Update Query that references a field in the Update To row from either a crosstab query, select query, or subquery that contains totals or aggregate functions Note: By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions.

This works even when the query returns records from more than one table. When the query is a Union query, you cannot update data in the query. When the Unique Values property of the query is set to Yes, you cannot update data in the query. To work around this problem, set the Unique Values property of the query to No. Skip to content Menu Close Why is my query not updateable? Jacob Wilson Table of Contents 1 Why is my query not updateable?

If the table exists, it is replaced. A dialog lets you specify the name of the output table:. Once you create the temporary table, using it rather than the summary query allows the two previous examples to work:.

Displays Updateable Records example 1. Updates the [TotalSales] Field example 2. An alternative to Make-Table queries is an Append query. Append queries let you insert records from a query into an existing table. If you just have a one step process, there usually is not much difference. However, if you have multiple steps, Append queries have a clear advantage. For these reasons, you should become familiar with Append queries.

Follow these steps to setup your application to use Append queries:. Append Query: Notice how field Expr1 goes into the nicely named "Sales" field. Both are quite simple. The code below lets you run any action query including Update, Delete, Make-Table, and Append queries:.

If you use temporary tables, you need to consider multi-user issues. You cannot have two people creating and using the same temporary table in the same database. Each user must have their own database where temporary tables are created and used.

The database can be linked to a shared database containing the data, but your application must be in a separate database. This is the proper way to design an Access application anyway. For more information on this type of architecture, read our paper on Splitting Microsoft Access Databases to Improve Performance and Simplify Maintainability. Make sure you spend the time to understand how to use Access queries.

They are extremely powerful and eliminate a lot of unnecessary coding. They are also much easier to debug than module code. The use of temporary tables is fundamental to working around the annoying non-updateable queries problem, and also for performing advanced data analysis.

He has spoken at a variety of conferences and user groups. All rights reserved. This information may not be republished, reprinted or retransmitted in any form without the express written permission of FMS Inc. The information provided in this document is provided "as is" without warranty of any kind. Latest Newsletter Sign up. Blog with us and subscribe to our RSS feed. Follow us on Twitter. The most popular Microsoft Access Number Cruncher. Celebrating our 33rd Year of Software Excellence.

Vienna, Virginia Privacy Policy Webmaster. Toggle navigation. Products Microsoft Access Products. All Microsoft Access Products. Total Access Admin. Total Visual Agent. Total Access Analyzer. Total Visual CodeTools. Total Access Components. Total Access Detective. Total Access Memo.