Workaround to record not editable due to Sum function? (1 Viewer)

[dlambert]

Hello everyone.
I am having a recurring problem when developing my database and was wondering if there was a standard workaround.
I have for example a query that i need to display in Datasheet View. Ideally as part of the query i would have one of the fields come from another query that has a sum function. That all works as it should, apart from the fact that as soon as the field that comes from the Sum function in the other query is displayed in the form Datasheet View, the records become not editable. Of course i do not want to be able to edit the summed field, but only the other fields in that query entry.
It is important for me for the data to be able to be edited directly from the Datasheet View. Unfortunately in order to be able to edit i have to forego seeing the summed field, which is not critical for me, but would be really nice if i didn't have to.
So, would anyone know of a workaround to this problem? Some special way to get the required summed data into my query output without making it not editeable?
I am thinking to go down the route of adding a new "Sum" field to one of the original tables, running an update query with the Sum value into that field, that using that in my desired query...
Any suggesting would be much appreciated

 

[ebs17]

Use DSum in the query as a substitute for a subquery.

 

[The_Doc_Man]

Eberhard's suggestion would work, with this warning: A DSum has far higher processing overhead because it has to execute a "hidden" query for each record. IF you are dealing with any processing through this query, you would do better to have TWO queries - one for display that includes the Sum element and one for processing that lacks the Sum element.

Your idea of depositing a sum into a field in the table would work, but note that the "normal" way you would do this would ALSO involve a query that contained an aggregate function and thus ALSO could not be updated. Also, the sum would violate normalization and thus would inherently require more maintenance to keep it current. (It's a perfect example of why you normalize tables.)

Then there is the question of whether the SUM - regardless of which flavor of SUM you choose - is the sum of elements from that same table. If so, your worksheet would be a query with an aggregate field, so it also could not be updated.

 

[Pat Hartman]

It is important for me for the data to be able to be edited directly from the Datasheet View.

I hope by this you mean a form in DS view because you NEVER, EVER want to edit tables or queries directly. We use forms because the form's have event procedures where we can validate the changed data before it is committed.

 

[ebs17]

I only mentioned one simple(!) possibility out of many possibilities.

You could also display your records in a list box (with total) and display the activated record (then without total) in a subform for editing.

You could have two subforms side by side, one with the records, one with the associated totals. These two subforms would then have to be synchronized, certainly a greater challenge, but also a nice solution that not everyone has.

And you should always remember: In Access, creating forms is very easy, there can also be a few more. Not everything has to take place in a "table", we are not in Excel.
Instead of a workaround you could also build a highlight.