Query Not Updated (1 Viewer)

[looma123]

Hi,

i run access system for our invoicing. i have 2 tables
1. invoices
2. Collection

for each row (invoice) in table 1 there might be multiple rows (collection) in table 2.

to show the pending collection i have to create 1 query for table 2 (Collection) with sum of the amount column, then link it to a 2nd query sothat it will total the collection for each single invoice and i can show only the uncollected invoices/partialy collected invoices.

my problem is that in the query showing the pending coleltion i cannot update any data "The record set is not updatable", any one have solution?

 

[June7]

Not supposed to save aggregate data - calculate it when needed. Otherwise, use VBA manipulating recordsets to save or use Domain Aggregate functions in UPDATE action or write data to a 'temp' table and use that as source for UPDATE action to permanent table.

This is a common topic.

 

[looma123]

Not supposed to save aggregate data - calculate it when needed. Otherwise, use VBA manipulating recordsets to save or use Domain Aggregate functions in UPDATE action or write data to a 'temp' table and use that as source for UPDATE action to permanent table.

This is a common topic.

Thanks for your Reply, could you please give me some hint, cannot understand well.
manipulating recordsets

 

[June7]

Recordset approach may be too complicated.

Review https://answers.microsoft.com/en-us...query-in/c27ab6f2-6f21-45a9-9208-4c39f9956536 and https://stackoverflow.com/questions...able-using-update-and-aggregrate-sum-function

Again, this is probably a bad idea. If can be calculated for UPDATE, it can be calculated when needed.
More info http://allenbrowne.com/AppInventory.html