Calculate discounted price based on letter code.

  • Thread starter Thread starter bevank
  • Start date Start date
B

bevank

Guest
I' m new to MS Access. Pardon my ignorance.
I have a table with four columns: part number; part description; list price; and discount code, eg., "A", "B", "C". The "A" discount is 0.18, the "B" discount is 0.21, and so on. Each part is assigned a discount code. I want to calculate the actual discounted price for each item. I have created a query which does the math, but the results are displayed in different columns of the query.
First column--BTP-A: IIf([TRANSFER PRICE INDEX]![disc]="A",[TRANSFER PRICE INDEX]![price]*0.18)
Second column--BTP-B: IIf([TRANSFER PRICE INDEX]![disc]="B",[TRANSFER PRICE INDEX]![price]*0.21)
I need to have the results displayed in the same column of the query for each part.
All suggestions will be greatly appreciated! Thanx.
 
Not clear what you want to do.

If you want to have four different prices for your parts, you are either going to have four columns, or four rows.

I think, however, that you are approaching this wrong. You should be calculating the discounted price by measuring the list price against the discount at the time you need it. This means that your calculation will automatically reflect new prices and discounts without holding redundant data.

Acces is not a spreadsheet. You hold data in tables, you carry out calculations in a query or a form. If you need to hold the result of historical calculations, update a history table as part of your query/form operation.
 
I think you want to do a nested iif statement

Try this

IIf([TRANSFER PRICE INDEX]![disc]="A",[TRANSFER PRICE INDEX]![price]*0.18,IIf([TRANSFER PRICE INDEX]![disc]="B",[TRANSFER PRICE INDEX]![price]*0.21))

etc for each letter code
 
That's fine - just remember to put the value_if_false in too.

IIf([TRANSFER PRICE INDEX]![disc]="A",[TRANSFER PRICE INDEX]![price]*0.18,IIf([TRANSFER PRICE INDEX]![disc]="B",[TRANSFER PRICE INDEX]![price]*0.21,0))

That way, if [disc] is neither of those letters, the query will return a 0 value.
 
Say your discounts are:

A= 0.18
B= 0.21
C= 0.24
D= 0.27

Easy way to compute it is:

Net = ListPrice * (1 - Discount)

For example

For A: Net = ListPrice * 0.82 | (1 - 0.18)
For B: Net = ListPrice * 0.79 | (1 - 0.21)
For C: Net = ListPrice * 0.76 | (1 - 0.24)
For D: Net = ListPrice * 0.73 | (1 - 0.27)

So, if your table looks something like this:

Part# Description List DCode
A01 Widget1-New $18.50 A
A02 Widget1-Used $18.50 B
A03 Widget2-New $21.00 C
A04 Widget2-Used $21.00 D

…use the Choose() function in a query to return
the net price.

If you'll lookup the Choose() function in the HelpFile, you'll see that the Index portion requires a integer between 1 and the number of choices (which, in this case is 4 (A, B, C or D).

Asc("A") = 65
Asc("B") = 66
Asc("C") = 67
Asc("D") = 68

So:
Asc("A") - 64 = 1
Asc("B") - 64 = 2
Asc("C") - 64 = 3
Asc("D") - 64 = 4

Armed with this knowledge, you can go on to produce the following query:
Code:
SELECT tblParts.[Part#], tblParts.Description, tblParts.List, tblParts.DCode, [List]*Choose(Asc([DCode])-64,0.82,0.79,0.76,0.73) AS Net, 1-[Net]/[List] AS Discount
FROM tblParts;

Give it a shot, it will work for you!

Bob
 

Users who are viewing this thread

Back
Top Bottom