Access when is a query updateable

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.

The database was opened as read-only or is located on a read-only drive. It is a SQL pass-through query. It is a query whose UniqueValues property is set to Yes. Cartesian Joins that is, a query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view. Query based on three or more tables in which there is a many-to-one-to-many relationship.

Note: Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset Inconsistent Updates. Calculated fields. Even if the query itself is updateable, if a column in a query is based on a formula, the field cannot be updated. However, if the other fields in the formula are updated, the calculated field will automatically update.

Recordsets Are Updateable Under Certain Conditions Some queries, especially those involved in a Join, will not be updateable under some conditions, but will be under others. Query based on a Join of tables with no Relationship. New records on the "one" side that are duplicates of other "one" side records. Conclusion The causes of non-updateable recordsets are many and varied.

Johnny Bones Johnny Bones 8, 6 6 gold badges 40 40 silver badges bronze badges. Great explanation Johnny Bones! Sign up or log in Sign up using Google. Sign up using Facebook. You have decided that you will use an Update Query to change all of the relevant records in your Products table. The original Products table before we run the Update Query. Remember that the update query will permanently update records from the specified table s , therefore it is very important that you have backed up the table s or database before running this object.

So if at least one case exists in your contacts table c where multiple c. In this case, it is not the returned value c.

Thank you, Doc Man. I think you're getting me somewhere. Interestingly, though, c. If your contact name is indeed declared as unique, then Access should have been able to determine that and allow the update. The cardinality would have been 1 when it evaluated the sub-query.

I have used that kind of syntax myself for some update and insert queries, so I know it is legal. JHB Have been here a while. Local time Today, Joined Jun 17, Messages 7, Create a query call it FindContact to pick the Id from the Contact table.

Code: Copy to clipboard. JHB, That won't work because there is no join between the two tables. The cross join produces a Cartesian Product and those are not updateable. Thanks for the follow-up, Doc Man. Here are my responses to the points you raised. The only relationship between between the two tables is the very field that we're trying to update, so join wouldn't work here. I've used join with updates before with great success, but this is different.

I've come to the conclusion yet unverified that subquery's condition may have to be the primary key for it to work. Follow us on Twitter. The most popular Microsoft Access Number Cruncher.

Celebrating our 35th 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. Total Visual SourceBook. Total Access Speller. Total Access Startup. Total Access Statistics.

Multi-Product Suites. Total Access Ultimate Suite. Total Access Developer Suite. Total Visual Developer Suite. Visual Basic 6 Products. Total VB Statistics. Total VB Enterprise Suite. Other Products. Sentinel Visualizer. Total ZipCode Database. All Products: Demos, Catalog, Awards, etc. All Products. Product Awards. Forum and Ticket Submissions.

Support Options.