Conditional calculated field.

**ser01** · 01-03-2011, 06:53 AM

Hi all!
I am trying to build a query with a calculated field. The fields involved look something like this:

Code:

Ticket    Price    TransLink    Calculate:
701910043            
701910249    81.74        
701910286    81.137        
701910451    1.00392        
701910486    1        
701914131    0.97686        
701914543    0.9706        
701922763    1.00372        
702002522            
702002523            
702002521            
702002520            
702008654    1.00304        
702116307    0.98146        
702116373    82.02        
702130962    1.00179        
702132472    63.189        
702132502    62.043        
702137835    1.00092        
702196105    1.33332        
702196801    1.33461        
702197288    81.355        
702201320    1.33317        
702201487    1.33041        
702201976    1.00544    701910451    
702201977    1.00544    701922763    
702201978    1.00544    702008654    
702201979    1.00544    702130962    
702201980    1.00544    702137835    
702202114    1    701910486

What I need is to loop through the [TransLink] filed until i find a value that matches the current [ticket] field. once the matching value is found, the [calculate] filed would receive the price value corresponding to the matched [TransLink] value.

I tried to accomplish this with some of the built in functions with no luck. I may need to use VBA to do what I need, but its a bit too complicated for me. Any help or suggestions would be appreciated.