Update Query

[Guest]

I have a query which is calculating payments due to Clients.

I need to take a "snapshot" of the amounts to record how much they've been
paid to-date, so that I can keep the running total and calculate the extra
payments next month.

It seemed obvious to create a table with ID and Payment, then run an update
query which would put value of the EarnedToDate field from the query into the
Payment field of the table.

However it appears that, since the EarnedToDate field is being calculated,
it cannot be used to update the Payment field.

Oh, and the calculation is so hideously complex that I can't just add it in
to the "Update To:" section of the query.

Is there a way to get around this and use the calculated value to update the
table?

Thanks in advance

Adrian

 

[Ken Snell [MVP]]

Can you tell us what the query's SQL statement is? It's not clear what
you're doing with the data.

 

[Guest]

Thanks for the offer, Ken, but I've given up on the idea. I don't think it's
going to be possible to do what I wanted.

I've managed a workaround of creating a Make Table query which just dumps
the current state of the query to a table. I can then use that table to
adjust next month's payments and then run the Make Table query again,
overwriting this month's figures.

If I ever get a spare moment or two (ha!) I'll see if I can put together an
(simplified) example of what I'm trying to do and post that here.

Thanks,

Adrian