Compare two fields and populate thirds field if the two field match.

[VikingMax]

I am working on an Access DB., I would like to compare two fields and if they match I would like to populates this field with data from a third field. I have tried IIF and work great, but I have to many nested IIF statement.



This is for a workout program for our atheltes. Ex: Johnny has a bench max of 145 lbs. He will use the following workout - 90 , 100, 115. I want the db to compare the workout to another field and then update the Weight on Bar if the workout and field are equal



Table 1 has player and bench max. I created a query of the bench max to produce the workout weight. Table 2 has the weight, say 90lbs, and the amount of weight that should be put on the bar to get to this weight. This table goes from 45 lbs to 650lbs. Johnnys workout weight is 90 lbs so he should put 2 -10's 0o the bar. The cells that are colored are what I want the db to produce.
Hope this makes sense.

Thanks for any help...

Robert

[June7]

Your explanation of what you want to do is not very clear. You want to do a search of Table2 to get the weight values that should be added to attain total weight of apparatus? That should not be hard with a DLookup expression. Need better understanding of data and forms. If you want to provide project by attaching to post, will look at.

[VikingMax]

attached is the db. if you look at the Workout form, you can see that Charles Simpson has a Bench Max of 140. His warmup weight is 85lbs and the weight for the bar is 2-10's. This (weight for the bar) was acheived by and IIF statement, but the IIF statement runs out when I get to 115 and I need to goto 600. I want the db to auto updtae each of the weights for the bar based on the warmup or workout weight.

Hope this helps and thanks for any help.

Robert

[June7]

Try this in the Workouts query:

Expr2: DLookUp("bwWeights","weights","bwWeight='" & [bwWarmUp x 10] & "'")

Note the use of apostrophe delimiters because you have the bwWeight field set as a text instead of number type.

[VikingMax]

You are the man..... the only problem I had was [bwWarmUp x 10]... should have been [bWarmUp x 10].....

Thanks for all your help.......

Robert