Access calculating discount rate

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?

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

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!

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.