Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    calculating discount (Access 2000)

    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 ?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating discount (Access 2000)

    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.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating discount (Access 2000)

    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
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating discount (Access 2000)

    You'll need a series of queries for that. See the attached version.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating discount (Access 2000)

    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

  6. #6
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating discount (Access 2000)

    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 ?
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating discount (Access 2000)

    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.
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating discount (Access 2000)

    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";"OrderI D=" & [Orders].[OrderID]);0))

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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating discount (Access 2000)

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

  10. #10
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating discount (Access 2000)

    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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: calculating discount (Access 2000)

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

  12. #12
    2 Star Lounger
    Join Date
    Sep 2006
    Posts
    166
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: calculating discount (Access 2000)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •