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!!
-- 
tgl

On Mon, 19 Mar 2007 14:10:30 -0700, tgl wrote:

> 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


-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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....


"fredg" wrote:

> On Mon, 19 Mar 2007 14:10:30 -0700, tgl wrote:
> 
> > 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
> 
> 
> -- 
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>