Calculation

[Erictsang]

Dear all

I have a question on Access

I have a data from Excel . see attached



Date of tour and Tour type

I have price for each Tour and promotional quote for each tour

For example
Tour 1 - price is $10
Tour 2 -price is $20

Promotion -
C100 - less 10%
B200 - less 20%
E025 -less 25%

I know i can use express builder to make the calculation of tour.
but when i come to the promotion code, how can i address the C100 and total *0.1 , if B200 , then total * 0.2 ??

Eric

[kd2017]

I'm not entirely clear on what your situation or what your database is like but it seems like you need a promo code lookup table. But It sounds like you're wanting to decode the promo code string to figure out the value?

The excel table is confusing too. Does 12-oct show 3 different tours on the same day, or one tour that has 3 types?
A promo code is good for just that day? That tour(s)?

[ArviLaanemets]

CHOOSE(MATCH(Promo,{"C100";"B200";"E025"},0),0.1,0 .2,0.025)
I'm not sure about list delimiters here - maybe they must be commas for US settings? For me both delimiters for function parameters and list delimiters are semicolons.
Replace Promo in formula with cell reference or with table column name - depending on type of formulas you use (table formulas or old type ones).

[Erictsang]

Arvil


I have made total:Nz([Tour 1])*20+Nz([Tour 2]*20)

Then i made a discounted Price .... see attached

the discounted price should be based on what in the [promo] ,


I tried this in the "disccounted field" -Text31 :CHOOSE(MATCH([Promo],{"C100";"B200";"E025"},0),0.1,0 .2,0.025), it does not work

Please kindly enlighten me further

Thanks

[Erictsang]

KD

the user can take all "tours" in one day , i need to add all the cost together and less the discount from Promo

See below

Eric

[ArviLaanemets]

Arvil
I tried this in the "disccounted field" -Text31 :CHOOSE(MATCH([Promo],{"C100";"B200";"E025"},0),0.1,0 .2,0.025), it does not work

Sorry, I was just in excel forum before reading your post, and your datasheet (?) image was too worksheet like

The simplest solution in access will be an UDF (i.e. your own function) IMHO.

[Erictsang]

i do not understand what you mean?!

[Erictsang]

Dear all

I have used the Switch function to solve the problem

However, the result was calculate in an unbound field, how can i store it into the table?

[Bob Fitz]

It is usually considered to be bad practice to save calculations, better to do the calculation whenever and wherever it is required
See: http://allenbrowne.com/casu-14.html

[Erictsang]

I have a question again


I made a table for the promotional code :

Promo_code -Table


[code] [dscount]

C100 10%
B200 15&

Then , i made a Form - Master_F

My idea is to calcuate the discounted rate,i.e total * discount % that represent by the promotion code.(refer to the above
In this case, C100 ,10% discount, we expect 60*10% = 54

=DLookUp([Promo_code]![Discount],[Promo_code]![Code],[Promo_code]![Code]=[Promo])*[Total]

Can anyone be so kind to enlighten me

[Bob Fitz]

The sytax to return the amount of discount from the table would be something like:
=DLookUp("[Discount]","[Promo_code]","[Discount] = '" & Me.[Promo] & "'")

[Erictsang]

Strange
it prompts Error

[Erictsang]

thanks and with the following



=DLookUp("[Discounted]","[Promo_code]","[Discount] = '" & Me.[Promo] & "'")

I tried to take away the me. also error

Eric

[Bob Fitz]

Can you post a copy of your db with a few dummy records to illustrate the problem

[Erictsang]

I have solved it with the following

Round(DLookUp("[Discount]","[Promo_code]","[code]= '" & [Promo] & "'")*[Total])

I have a silly question, originally, i put down 10% as the discount rate in the Promo code 's discount
However, when it comes to calculate [total]* 10%, it did not come out 54

I have to change the discount to 0.9 , how can i solve it