Access: Workarounds for updating data in not updatable query?

Question

tldr: Can not update records from query because of aggregate functions. What workarounds do you suggest?

I have a table containing decision criteria to which a user can assign a relative weight. I calculate the absolute weight in an SQL query using an aggregate function (as described here Divide the value of each row by the SUM of this column).

qryDecisionCriteria
name             relative_weight    absolute_weight (calculated)
price             2                 50 %
quality           1                 25 %
experience        1                 25 %

I would like to present the query result in a form, where the user can update the relative weights, and then sees the absolute_weights. However, the query results are not updatable, because the query involves an aggregate function.

What alternative methods or workarounds could I use, so that a user can edit relative_weights and view absolute_weights as a kind of visual feedback?

I read about temporary tables here http://www.fmsinc.com/MicrosoftAccess/query/non-updateable/index.html but I'm not sure, how to implement this. Maybe I could also create an additional "edit form" based on a simple query, that is automatically invoked when the user selects a record in qryDecisionCriteria data? Or maybe just display data from two queries (one updatable, one with the calculated field) next to each other in the form?

Which options would you recommend and why?

Brad Brad · Accepted Answer · 2014-04-09T18:33:58

Make the Record Source for the form the updatable base query. In the text box which shows the calculated absolute weight set the control source to

=DSum("relative_weight","<base table name>")/Forms!<Form Name>!relative_weight

You'll need to be sure that you do two things with this

When you drag fields onto a form in Access it makes the name of the control the same as the control source column. this is really annoying and can cause a lot of headaches. Rename your control to something like txtColumnName. That way Forms!<Form Name>!relative_weight is guaranteed to reference the field and not the textbox.
in the AfterChange event for the relative_weight textbox you should add an event handler in which the following code is run
```
txtabsolute_weight.Requery
```
This will make sure the formula is recalculated whenever someone changes a weight. Otherwise they need to hit F5.

Access: Workarounds for updating data in not updatable query?

1 Answers