Updateable query error

[Guest]

I am trying to update the BalanceAmt for an AcctNo in a BalanceForward table
with the results of a NewBalance query. I get the error: “Operation must
use an updateable queryâ€. I have been unable to figure out what to do to fix
this.

The BalanceForward table contains AcctNo, BalanceAmt, and LastInvoiceDate.
The NewBalance query contains AcctNo, BalanceAmt, and NewBal (where NewBal
is itself the result of a query that only uses INNER JOINs).

The query below is what Access generates:

UPDATE BalanceForward INNER JOIN NewBalance ON BalanceForward.AcctNo =
NewBalance.AcctNo SET BalanceForward.BalanceAmt = NewBalance.NewBal;

Can you help please??

 

[Guest]

This is a very complicated subject. I suggest that you open up Access Help,
go to the Find tab, type in "updatable" (lower case u) or "About updating
data", and then scroll down to "When can I update data from a query?" or "Why
can't I edit data in my form?". There you will find a lot of, possibly too
much, information on the subject. In a nutshell, if the query is based on one
table or tables with a one-to-one relationship, you will be able to edit or
delete records. If it is based on two or more tables with a one-to-many
relationship, you 'should' be able to edit or delete records. If you have
three or more tables based on many-to-one-to-many relationships, you will not
be able to edit or delete records. This is just the highlights. Help has much
more information.

 

[Gary Walter]

In addition to Jerry's sage help,
it might help if you post the SQL
for NewBalance query.

Any aggregation will cause update
query to choke. It may be that you
can use a (slow) domain function
like DSum() in this query to make
your update query work...

 

[Guest]

I had read that Help page (several times) and thought that if my query
results came from an Inner Join that I was okay, but no.
I finally had to create a temporary table into which I was able to put the
updated BalanceAmt in the records, then rename the temporary table to the
original one.