If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
update query based on two fields
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. |
#3
|
|||
|
|||
update query based on two fields
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. |
#4
|
|||
|
|||
update query based on two fields
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. |
#5
|
|||
|
|||
update query based on two fields
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. |
Thread Tools | |
Display Modes | |
|
|