update query based on two fields

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.

"Versace77" wrote:

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.

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.


"Versace77" wrote:

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.

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.


"Versace77" wrote:

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.

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

"Jerry Whittle" wrote:

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.


"Versace77" wrote:

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.