Lookup Table Function / Array

[sdimaggio]

I am trying to figure out the best way to have access select a price based on a certain perameter. i.e. a sliding scale.

I have a PriceTable with 20 fields which are price points based on a delivery having a certain percentage

Field1: 0 to 5% (input is $0.15)
Field2: 6 to 10% (input is $0.25)
Field3 thru Field19
Field20: 96 to 100% (input is $1.00)

When a delivery comes in I want to have access recognize the % and apply the right price. For example a delivery with 7% should be $0.25 and a delivery with 99% should be $1.00.

I could use the dlookup function but I would have to repeat it 20 times in my querry? Is there an easier way?

Thanks

Steve

 

[Craig0201]

Steve,

Write a custom function using SELECT CASE

Function PricePoint(Percentage As Double) As Currency

Select Case Percentage
Case Is < 5
PricePoint = 0.15
Case Is < 10
PricePoint = 0.25
etc.......

End Select

End Function

This can then be called like any other function.

Hope that helps

Craig

 

[sdimaggio]

Thanks, but I need a bit more clarification.

Does the Select Case take into consideration that each of my prices vary for each delivery. For example One customer who comes in with a delivery on the 0-5% scale may be charged $0.15 the next customer may be charged $0.45.

I tried to compensate for this by doing the following:

Function PricePoint(Percentage) As Currency

Select Case Percentage
Case Is < 5
PricePoint = [0 to 5%]
Case Is < 10
PricePoint = [6 to 10%]
End Select
End Function

If my delivery record field is called [Delivery %] would the expression be:

PricePoint([Delivery %])

When I tried this I got an error.

Any suggestions

 

[Craig0201]

Steve,

Nom, it doesn't account for this.....but then, neither does your post!!!

You will get an error.....you changed the function to return a string when I defined the return as a currency........

Sounds to me like there may be some planning issues here....

Your table design should be something like below....

Customer table should have a link to a PriceBand table....

Each price band should be linked to a PriceRule table.....

So.....

Customer A is on price band A....Price rule 1 breaks down into x sections......

It should then simply be a matter of setting up a query to work out which section of the rule they are in and passing that back......

Now your function should be......

Function PricePoint(Percentage As Double) As String

Select Case Percentage
Case Is < 5
PricePoint = &quot;0 To 5%&quot;
Case Is < 10
PricePoint = &quot;5 To 10%&quot;
etc.......

This will return your price section.....

This can then be used as a parameter in association with your customer ID to find the rule in a simple SELECT query.....

So you'd have all three tables, joining PriceBandID in your Customer table to PriceBandID in your PriceBand table and PriceBandID in your PriceBand table to PriceBandID in your PriceRuleTable.....you can then the function as a parameter against your PriceRule with a reutrn of the PriceRuleValue........

Make any sense?

Craig

 

[sdimaggio]

Craig,

My real problem is that each price band will always have a different price. Salesmen A may price 0-5% at $.15 and Salesmen B may price 0-5% at $.25 it is never the same.

The brackets I had around [0 to 5%] is the field of the PriceTable and is not a string but a number.

Example:
PriceTable
Customer A Field: 0 to 5% = $0.15
Customer B Field: 0 to 5% = $0.25

Customer A Field: 6 to 10% = $0.20
Customer B Field: 6 to 10% = $0.35

When a delivery comes in and the percentage is 8% it should pull the correct price from field 6 to 10% depending upon the customer.

I think your example illustrates a fixed price in band section.

I will continue to noodle on this but do appreciate your help and any other suggestions you may have. Thank You.

Steve.

 

[Craig0201]

You have a field [0 To 5%]? In which case you are normalised incorrectly......

Your table design should be.....

Customer Table
Has fields
CustomerID
PriceBandID

PriceBand Table
PriceBandID
PriceBandText i.e. 0 to 5%

PriceRateTable
PriceRateID
PriceBandID
PriceRate

So a PriceBand can take many values and a PriceBand has many bands within it.

Now you may even have more than one salesPERSON (no sexism, please!) to a single company with differing rates but that still isn't a problem with proper normalisation....

It's just a matter of getting your table design right....and it sounds like it isn't!!!

Craig

 

[sdimaggio]

I'll take a look.

Thanks for spending the time this.

Steve