Selecting a field based on the result of another

[jasn_78]

Hi

I am currently designing a query and I need to be able to select a different field from one table depending on the value of another table e.g if the customer type = a then select price 'a' if customer = 'b' then select price 'b'. I have tried both if and case statements and have had no luck so any ideas would be greatly appreciated.

 

[aqif]

Hi

If there is a relationship between price and customers than maybe nested query will solve ur problem, it goes like:

Select Balance from TblBalance where Customer= (Select Customer from TblCustomer where Customer="b");

Hope it gives u some idea.

Cheers!
Aqif

 

[jasn_78]

aqif

Thanks but it doesn't seem to work. What I currently have is a customer type and then 5 seperate fields listing each customers discount for an item depending on what type of customer they are. Therefore I cannot create a relationship from customer type to each discount as it would be impossible to link one field from one table to 5 fields in another single table or is it?

Sorry to confuse you but while I am relativley new to Access I have a high level of skill with Oracle and SQL it is just these clients need it done in Access.

 

[jasn_78]

Pat: So are u saying have just one field with each level of discount or have 5 seperate fields as different products also have different levels of discount not just on the customer.

 

[jasn_78]

Pat:

If this is of any help here is a small listing of the way tables are at the moment

tblCust: Customer Number
Name
Discount Type

tblItems: Item Number
Description
Base Price

TbleItemsDiscount:
Item Number
Disc A
Disc B
Disc C
Disc D

of course there is more fields in the 1st two tables but they are just description information.

if you have anymore ideas i would be very appreciative. and thank you for putting up with my questions i know they must be getting annoying

 

[jasn_78]

Pat:

Thanks but got it sorted

thank u for all your help