Developer Forum

Hello:

I'm looking for help with an update query, i am looking to have a field
changed based on the value in two other fields on the same row, Column G and
Column H. I am familiar with doing an update query if it was based on the
value of just G or just H, but not both at the same time. here is an
example:

If G and H is 60 and 30 then i want column J to be 678900

Col G Col H Col J
60 30 678900
60 15 XXXXX
25 30 XXXXX

The 60, 30, is just one possible combination out of around 120.

Hope this is explained well enough. Thanks.

Sorry, I wanted to add also, if there was a way to do this using one query
for the 120+ combinations so i don't have create 120+ separate update
queries. thank you.

>> The 60, 30, is just one possible combination out of around 120.
You need to create a translation table containing the three columns. Then
use it in your update query.
In query design view it would look like this --
FIELD Col G Col H Col J
TABLE YourTable YourTable YourTable
UPDATE TO Tranlate.[Col J]
CRITERIA Tranlate.[Col G] Tranlate.[Col H]

--
Build a little, test a little.

If the combination of Column G and H means something, you shouldn't have it
in J. Instead you should figure it out on the fly with a query.

If this was just 2 or 3 combinations, an IIf statement would be OK. If it
was a few dozen, maybe a Case statement. However you say that there is 120
combos. In this case you should list that data in another table and join them
on G and H to find out J.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Karl and Jerry, thank you both for helping me here and sharing the sound
advice.