Updating a field with a calculation with two other fields on separate tables.

[Dave0291]

Hey,

I have a problem and I'm kind of new to Access. I'm trying to update a field on one table by multiplying two fields from two separate tables. Now the problem is that every time I try an update query, I get this "Query must be updatable" and I can't make a calculated field because my operands are from other tables. Is there any way to work around this?

Thanks!

[Rabbit]

You shouldn't store calculations in the first place. You can just calculate it on an as needed basis.

[NeoPa]

That's a very good point. If you have trouble understanding why then see Database Normalisation and Table Structures.

If you're interested in updatable queries then see Reasons for a Query to be Non-Updatable.

[Dave0291]

Thanks for the replies guys.

I managed to fix the problem by running two different update statements on that column.

[NeoPa]

In that case I'm sorry to say you've missed the main point here. It sounds strange, but you'd have been better off failing to find an approached that updates any fields which are the results of calculations using data already available to you, and looking at the underlying principles instead. I rather suspect you will come across far more problems down the line with this approach than you would have if you'd paid more attention to the advice of those with the experience to know.

Good luck anyway :-)

[Dave0291]

I appreciate your advice and I would of followed it if I hadn't "solved" my issue before your messages came in. Although my solution was only for a one time update, it was pretty messy. I read those articles after though and they helped a lot. Hopefully this problem comes up again so I can take the right approach this time.

Thanks for all the help.

[NeoPa]

To be fair - understanding how to do both will help in future. Neither will be wasted learning ;-)