calculating discount (Access 2000)

Topic

New Reply

I need to build a function that calculates the liters sold within the month for a given client and depending on the quantity sold from the beginning of the month till the day of the order to calculate the liters for that client and with the function FncExtra to determine the discount. For example for sales up to 205 liters 5 percent diso######, between 205 and 300 liters 7 percent discount. I have difficulties to determine the sales for a given client and I do not know in principle how to involve the montly sales in that acount.My original function is :
Private Function FncExtra()
If Me!liters.Value < 205 Then
Me!UnitPrice = Me!reseller – Me!reseller * 5 / 100
ElseIf Me!liters.Value between 205 and 300 then
Me!UnitPrice = Me!reseller – Me!reseller * 7 / 100
End If
End Function
However this is fo the whole wuantity and odes not take into account the month. Could somebody help me with ths complicated task ?

Reply | Quote

Replies

You don’t need a function for that, you can calculate the discount in a query. I have modified the query named “a” in your database – see attached version.

Reply | Quote

Thank you for you very interesting reply. I could use this powerful query and would like to ask you the following. How can I see the discounts received for each order, since the query gives only the total discount ? For example for the client aa I have :
CompanyName M TotalLiters Discount reseller unitprice orderid
aaa 2007 05 205 5,00% 30,00

Reply | Quote

You’ll need a series of queries for that. See the attached version.

Reply | Quote

I made a quick check and found out that it is exactly spot on,exactly what i want. i cannot stop wondering how a formidable task can be solved is such a way.It is awsome, this solutiion..Thank so much indeed showing to all how powerful Access is

Reply | Quote

Dear Hans

i am trying to implement the wonderful solution into my database. The subform of my dataabse is caled FOrder details extended.its record source is Query1. To this Query1 i tried to add on the query QueryAmount and to add its field NewUnitprice into the Query1.However i get the message “you cant go tothe specified record”.How can i avoid this problem ?

Reply | Quote

In your previous version, InvoiceDate was the date you wanted to use to determine the discount. In this version, InvoiceDate is blank (empty), so the queries I designed won’t work. If you want to use the OrderDate instead, you must edit qryMD to use OrderDate instead of InvoiceDate.

But there is a problem: since the query that calculates the discount is based on a totals query, it is not updatable, so you cannot use it in the record source of a (sub)form if you want to be able to edit the records. You can work around this by using DLookup to find the appropriate price.

See attached version.

Reply | Quote

Dear Hans

Thank you so much for your attachement.It really works fine in the stripped down database.I have another problem.When i stipped down the database, i didnt enter a code in the BeforeUpdate event of the form which is :
Me!branch0 = Me!branch0 – Me.cartons
This codes gives rise to an error saying :

The expression you entered has a field,control or property name that Microsoft Access cant find

This happened when i attached the lookup field to the query1,as the control source of the form :
NewUnitPrice: CCur(Nz(DLookUp(“NewUnitPrice”;”qryAmount”;”OrderID=” & [Orders].[OrderID]);0))

Could i look up the field from the form itself or it is the same ?

Reply | Quote

There is no field branch0 in any of the tables, as far as I can see.

Reply | Quote

Yes, i simply cut this field out since there are 10 such fields and i wanted to slim down the table in order to send it easier. The table is called Products

Reply | Quote

Sorry, it is impossible to know what causes the problem.

Reply | Quote

Dear Hans

i want to thank very much indeed , this is a great solution. The problems i am facing are of a quite different kind and i will send another thread for that.

Reply | Quote