0

Lookup table

Hi. Looking for help with a lookup table. I have two tables with three fields each. Table a has a.code, a.amount and a.tax; a.code and a.amount having values already.
I have lookup table b with fields b.code, b.amount and b.tax.
I would like to lookup a.code and a.amount in b.code and b.amount and return the value of b.tax to a.tax.
Any ideas?

4 replies

null
    • -
    • silver_bee
    • 4 yrs ago
    • Reported - view

    Found a workaround :-)

    • Jon_lee
    • 4 yrs ago
    • Reported - view

    What was the solution ?

    • Mconneen
    • 4 yrs ago
    • Reported - view

    Not sure why you are using BOTH the code AND the amount.. but it could be something like.. 

    a.tax := first(select b [Code = a.code and Amount = a.amount])

    • -
    • silver_bee
    • 4 yrs ago
    • Reported - view

    Thanks, Mconneen. It doesn't quite work, probably because I haven't referenced a record in b to a. This is what I want the query to do. I have hundreds of entries for each code, and each code has many amounts. The amounts are the same for all codes, but the third field, tax is different, and is the result and what I want to link the two tables by.

    @Jon; I simply looked up b.tax (show as tax) from a and constrained the selection so that only one record (and the only right one)  is available to select . Constraints: a.code = b.code and a.amount= b.amount.

    It works, but it would be much more elegant if the lookup was automatic. 

Login to reply

Content aside

  • 4 yrs agoLast active
  • 4Replies
  • 940Views