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.

 

[Pat Hartman]

If you normalize your table structure, this becomes a "piece of cake". You need a table to store customer type and discount.

tblDiscount
CustType
DiscountPct

example values:
A .10
B .075
C .15
D .05
E .02

Then include this table in your query by joining to it on CustType. This allows the calculation to always reference the same column but the value will change depending on the type of customer.

It wouldn't hurt to also use this table to populate a combobox (set to limit to list) that is used when adding a new customer to ensure that customer type is always valid.

The extra advantage of a normalized structure is that if you need to add a new customer type or change the discount percent, you just change the table. No coding is required.

 

[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.

 

[Pat Hartman]

What I'm saying is - normalize the data. Five fields holding the same type of data, named along the lines of Discount1, Discount2, Discount3, etc. is s sign of an improperly structured relationship.

In terms of relational data, there can be 0 of something, 1 of something, or "many" of something. There is no such thing as 5 of something. Once you have more than 1, you have "many" and "many" requires a separate table.

If products also have discounts, what determines them? Do you need another small table to hold discount codes for products or can you use the same ones I suggested for customer type? Are the discounts for products and customers cumulative or mutually exclusive? Is there a discount percent associated with a product that is only applied for customers of a certain type?

I can't possibly tell you how to build a proper discount structure with the information given. Take a look at the following paper to see if you understand how one-to-many relationships work.
http://support.microsoft.com/support/kb/articles/q234/2/08.asp?FinishURL=%2Fdownloads%2Frelease%2Easp%3FReleaseID%3D13473%26redirect%3Dno

 

[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

 

[Pat Hartman]

I'm assuming that the values of the Discount fields are percentages rather than dollar amounts. Is the value of DiscA different for ItemNumber 1 than for ItemNumber 2? If the answer is no, the discount pcts belong in the customerType table. If the values can be different for each ItemNumber, they need their own table with a row for each ItemNumber/Disc? combination. This second solution seems like a brute force effort but if you analyze the discount policy and discuss it with the appropriate users, you may find that there is a pattern and you don't need to store these for each Item. Maybe there's only three different sets of values:

DiscA DiscB DiscC DiscD DiscE
.02    .03  .05   .07   .10   
.05    .07  .09   .10   .15
.05    .10  .15   .20   .25

If the structure is similar to the above, you can store a code (A,B, or C) with the ItemNumber and retrieve the actual rates from a lookup table.

Or maybe, the users would be able to standardize the discount policy when they see how much work it will be to maintain it as you have described.

 

[jasn_78]

Pat:

Thanks but got it sorted

thank u for all your help