Access calculating discount rate

So I have a unit price, number of orders and different discount rates in percentages. When I try to make a field in a query to calculate what the discount for each one will be, it asks me for a parameter value for the discount rate and anything I enter in that is then applied to the entire page whereas i need varying discount rates for all. what do i do?

Do you want the user to type in the discount, or have the system calculate it based on a table of discount levels?

If the latter, are the discount levels different per product or productcategory?

-Tom.
Microsoft Access MVP
Phoenix, AZ

4 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Discount levels are different per product and are in a separate table already.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Then you need to create a Query joining this discount table, and use a calculated field in the Query. Just how you would do so depends on the structure of the tables - which you can see but which we cannot! More info please!
John W. Vinson/MVP

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Commonly in this sort of situation the current discount rate would be stored in a table which has columns for the ProductID, the upper operative quantity and the rate, the latter expressed a fractional value.  So if the product 'Widget' has a primary key value of 42 the rows in the table might be like this:

42          10    0
42          50    0.05
42        100    0.1
42    50000    0.2

i.e. there is no discount on products supplied in a quantity of 10 or less, the discount for an order of 11 to 50 is 5%, 51 to 100, 10% and above that 20%.  The upper value in the last case is an artificially high one which will never be reached.

You also need to consider whether the discount rate is time independently determined by the product and  the quantity ordered.  It generally will not be as discount rates might change over time, so you cannot simply reference the current discount rate as earlier orders would then have incorrect discounts once a rate is changed.  The rate must be assigned to a column in an OrderDetails table in exactly the same way as the current unit price is assigned for the same reason; see Northwind for an example of the latter.

The current rate can be looked up when a row is inserted into an OrderDetails table and assigned to a DiscountRate column with code along these lines:

Dim strCriteria As String

strCriteria = "ProductID = " & Me.ProductID & _
    " And Quantity >= " & Me.Quantity

Me.DiscountRate = DMin("DiscountRate", "Discounts", strCriteria)

The net price per order line can then be returned in a computed control in a form or report, or a computed column in a query with:

UnitPrice*Quantity*(1-DiscountRate)

In the albeit short time I worked in purchase and supply for a large manufacturing company many years ago it was also the practice to give different customers differential discount rates, frequent customers receiving higher rates.  This can be handled ether by including a factor in a column in the Customers able and looking it up and factoring it into the above expression to assign the current discount rate, or the Discounts table can also include a CustomerID column, i.e. it would model a relationship type between customers and products, with quantity as an attribute of the relationship type.
_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

4 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 
 

Question Info


Last updated July 11, 2020 Views 4,740 Applies to: