Aggregrate Amount in updateable query

B

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
 
A

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Updateable Query 3
Update-able query 1
Default Value on Form Help 1
Record Set Not Updateable In A Form 2
updateable query 8
Recordset Not Updateable 7
Updateable querie 2
Recordset not updateable 1

Top