Aggregrate Amount in updateable query

[brwn.fred]

Hi,
I want to have a form based on a query which has all updateable fields
except one which will contain a quantity joined in from an aggregate
query. I understand that joining in this field would make the rest of
the fields not updateable. I also wanted to sort the results based on
this aggregate field.

Therefore, I was thinking about doing the following:
1) Don't join in the aggregate query so that the rest of the fields
will stay updateable.
2) Make the extra field a Function which will be defined in my form
module. When the form updates it will load the result of the above
mentioned aggregate query to static variable record set. When the
function is called, it will look up the value from that recordset.

So basically I will do the join myself.
Will this work and is there a better way?

Thanks,
Fred Brown

 

[Allen Browne]

As per your 2nd suggestion, use DSum() to get the calculated total into your
query.

For example, if your query uses the Client table, and you want to show the
sum of the Amount field in the Donation table, you would type something like
this into the Field row in query design:
DSum("Amount", "Donation", "ClientID = " & Nz([ClientID], 0))

If you have lots of clients, this will be slow, but at least the records
will be updatable.