Hello,

I am trying to run an update query that will update information in a new field in Table 1 based on existing information in Table 1 and Table 2. Not sure this is possible, but this was my attempt in SQL:

UPDATE Results
SET Results.VPopImpactRating = "High"
WHERE Studies.Vreach = "High" AND Results.EffRating = "Effective"

When I try to run the query, I get this message: Enter Parameter Value - Studies.Vreach

I realize update query may not be the mechanism to accomplish this, so any guidance is appreciated.

Thanks!

You need to do a join in your WHERE clause...

UPDATE Table1
SET MyField = 'X'
WHERE Table1.Field1 = Table2.Field3
AND Table1.Effectiveness = 4
AND Table2.SomeField IN ('A','B','C');

Thank you for your response. I want to make sure I'm understanding you correctly -

--> Table1.Field1 = Table2.Field3
*is this where I indicate how the tables are linked - the primary key in table 1 to the foreign key in table 2?

--> AND Table1.Effectiveness = 4
AND Table2.SomeField IN ('A','B','C');
*is this where I would put AND Studies.Vreach = "High" AND Results.EffRating = "Effective"?

thanks!

I'm still getting the parameter error...it doesn't seem to be recognizing "table2.field3" as another table...

Why?

Storing data in a field when it can be calculated on the fly in a query accomplishes three things: it wastes disk space (trivial these days); wastes time (almost any calculation is faster than a disk fetch); and worse, risks data validation errors. One of the underlying fields could change, and your calculated field will now be wrong, with no easy way to detect that it is wrong!

How are your tables related? What's the rest of the logic for calculating the impact rating (surely this isn't the only value)?

Thanks for your response. The project I created the database for is pretty complex, but to try to give you a very simplified overview of my tables:

There ~ 300 studies in my project, stored as a field in the Studies table each with a unique StudyID and several other fields that are specific to this level (the Study level). Each study can have up to 6 strategies (there are 25 possible strategies) and can have any number of outcomes per strategy (there are 30 possible outcomes, but you can have the same outcome for different populations). This information is stored in the Results table each with a unique ResultsID (and several other fields that are specific to this level - the Results level) and linked to its study by the foreign key StudyID. Each Result gets an Effectiveness Rating and some Results also get an Impact Rating. Both are based on certain qualities of the Studies and Results tables. I thought ahead and was able to incorporate the Effectiveness Rating calculation in the Form I created for data entry, but I did not have enough foresight to do the same for the Impact Rating. Thus my current situation. I ultimately need a Report that breaks the Impact Ratings down by strategy, so that's my thought for wanting to have it calculated and stored in the table and not just in a query.

I'm not sure I'm even explaining it well enough, but if you have thoughts about that I'm open to suggestions...thanks!

I'd suggest creating a three-field translation table with a field for VReach, EffRating, and the corresponding VPopImpactRating; you could then create a query joining all three tables - this, Studies and Results - to look up the appropriate impact rating. Putting all the calculations in a monstrous nested IIF, or an obscure and hard-to-maintain VBA function, is just going to be a maintenance nightmare down the road!

I'll look into this...thanks!