By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,358 Members | 2,957 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,358 IT Pros & Developers. It's quick & easy.

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

P: 33

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?

Jan 5 '12 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 10K+
P: 12,366
You shouldn't store calculations in the first place. You can just calculate it on an as needed basis.
Jan 5 '12 #2

Expert Mod 15k+
P: 31,494
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.
Jan 6 '12 #3

P: 33
Thanks for the replies guys.

I managed to fix the problem by running two different update statements on that column.
Jan 6 '12 #4

Expert Mod 15k+
P: 31,494
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 :-)
Jan 6 '12 #5

P: 33
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.
Jan 6 '12 #6

Expert Mod 15k+
P: 31,494
To be fair - understanding how to do both will help in future. Neither will be wasted learning ;-)
Jan 6 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.