sum field from one query to update a field in a table

[Guest]

I have a "main" table of names with a second table "payments" showing
payments, records are linked by ID field. in order for users in my office to
use the total payment amount in a form to filter etc... i need real data in
the main table, so hoped to do an update query.
I want to sum all payments grouped by id in "payments" table and use this to
update field called "total money" in "main table". i get error: not an
updateable query. can an update be done on another tables grouped or summed
data?
thanks for your help!!

 

[fredg]

I have a "main" table of names with a second table "payments" showing
payments, records are linked by ID field. in order for users in my office to
use the total payment amount in a form to filter etc... i need real data in
the main table, so hoped to do an update query.
I want to sum all payments grouped by id in "payments" table and use this to
update field called "total money" in "main table". i get error: not an
updateable query. can an update be done on another tables grouped or summed
data?
thanks for your help!!

There is no need to have a Total Money field in your Table..
Normally, storing calculated data goes against Access database
normalization rules.
Any time you need to know the total money, simply re-calculate it, on
a form or in a report, but do not store the result.:

=DSum("[Payments]","tblPayments","[ID] = " & Me![ID])

In a query, all you need do is a Total's query:

SELECT tblPayments.ID, Sum(tblPayments.Payment) AS TotalMoney
FROM tblPayments
GROUP BY tblPayments.ID;

Again, do not store the total.

And oh, yes. Some queries are not updateable.
See Access help:
Query + Troubleshoot queries + Select Query + I can't update data
from a query + Data can't be updated

 

[Guest]

thanks -
mostly we do total as a calculated field in forms and reports, but some
users at our office like to look up records by filtering a calculated field
from another table (payments), which i think you are saying you can't do.--

i.e in a form based on "main" which has a field that sum the payments from
"payments" table, one might want to filter on people whose payments total
over $100,000. or sorting by highest total "Z to A".

also, i did try "=DSum("[Payments]","tblPayments","[ID] = " & Me![ID])" as a
control but keep getting "#Name?". Our field refers to a subform with totals
based on payments and is: =[Subform: payments].[Form]![text160] where
"text160" is the total.

thank you again....

I have a "main" table of names with a second table "payments" showing
payments, records are linked by ID field. in order for users in my office to
use the total payment amount in a form to filter etc... i need real data in
the main table, so hoped to do an update query.
I want to sum all payments grouped by id in "payments" table and use this to
update field called "total money" in "main table". i get error: not an
updateable query. can an update be done on another tables grouped or summed
data?
thanks for your help!!

There is no need to have a Total Money field in your Table..
Normally, storing calculated data goes against Access database
normalization rules.
Any time you need to know the total money, simply re-calculate it, on
a form or in a report, but do not store the result.:

=DSum("[Payments]","tblPayments","[ID] = " & Me![ID])

In a query, all you need do is a Total's query:

SELECT tblPayments.ID, Sum(tblPayments.Payment) AS TotalMoney
FROM tblPayments
GROUP BY tblPayments.ID;

Again, do not store the total.

And oh, yes. Some queries are not updateable.
See Access help:
Query + Troubleshoot queries + Select Query + I can't update data
from a query + Data can't be updated