two dimensional lookup

[villan60]

Hi everyone

I need to choose a field of a table based on the value of another field in a query. eg.

Name Call type SellPlan Sellprice

Sell price comes from a table which has the different call types as fields, so I need to take the call type from the second field of the query to tell the query which col to get the sellprice for a sellplan.

Maybe I am doing this totally the wrong way, so any other ideas on two dimensional lookups.

Thanks
Dax

 

[Cagliostro]

select sellprice, sellplan from queryx where sellprice = searched_value John Fill

ivfmd@mail.md

 

[villan60]

This is a bit cryptic for me, I haven't been able to solve this yet.

THe one solution I could do would be to loop through the databse and assign the result of my function to each record. However I am sure there must be an easier/quicker way.

People must do two dimensional lookups all the time, eg in tax tables you have to select a field based on the person's marital status as well as a record based on the amount they have earned.

So I am sure there is a simple way of doing it.. hope someone can help

dax

 

[JoeMiller]

If your calltype field in the table your querying is related to calltype table, you can add the calltype table and drag the sellprice down from the main calltype table. No need for code, that's a basic one to many relationship you're describing.

HTH Joe Miller
joe.miller@flotech.net

 

[villan60]

Thanks for responding Joe Miller, but I think that you have misunderstood what I am trying to do.

you see, a call type does not have a selling price. A priceplan has a selling price for a call type. so to ascertain the price of a call I need to look up a table the intersection of sellplan(row) and calltype (column).

hope that is clearer.

Dax

 

[tlbroadbent]

Could you post some sample table data and the result you wish to obtain? That will help me understand your requirement. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.