Results 1 to 12 of 12
  1. #1
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2011
    Posts
    62

    Applying a discount to a simple invoice system - correct approach?

    Morning folks,



    I have a simple invoice system for customers and stock, following the usual table structure:

    tblCustomer (CustomerID, FName, SName)
    tblStock (StockID, StName, QtyInStock)
    tblInvoice (InvoiceID, CustomerID, Date,Paid)
    tblInvoiceDetails (InvoiceID, StockID, QtyOrdered)

    I would like to include a discount field, based on each Invoice totals - which is a calculated value. For example, if InvoiceTotal is > £200, then 5% discount, >100, then 2.5 % discount, else, no discount.

    I realise this would go into tblInvoice as follows:
    tblInvoice (InvoiceID, CustomerID, Date,Paid, Discount)

    My hesitation is the mechanism/approach i will use to calculate discount, based on the Invoice Total, and hence update the Discount field to the appropriate value.

    I was considering the following:
    1) Create a macro that will check the invoice total, on the form 'frmINvoice'.
    2) If <condidtion> is met, it would run an update query to update the Discount field in tblInvoice.
    3) Then i get stuck - how would this then update the calculated 'Total' value in the form frmINvoice, without having to go back and refresh?

    Correct me please, if my approach is all wrong.

    thanks

    tim

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    As you can always calculate this, I'm not sure you should store it.
    Probably the best route would be to store your discount structure in a discount table along with a expiry date for historical calculations.
    You can then always calculate the discount for any given time, and your structure isn't cast in stone somewhere in code.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to Minty's comment, where do you store the current Price of the Stock(Item)?
    How do you handle changes in Current Price when dealing with older records/invoices?
    You may change your Discount calculations at some time, suggest you consider options for same.
    You could also consider QtyOrdered, AgreedToPricePerUnit, DiscountPerCentApplied... on the InvoiceDetails.

    Just some thoughts for consideration.
    Good luck with your project.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    store or not to store? my own view is for anything financial store the actual value - might be the discount percentage, might be the amount in this case. either way if you don't store it and you decide to run off a copy invoice at a later date and you have modified the rates or the calculation, you will come up from a different figure. The same goes for taxes and prices - the rates change, but you don't want previous invoices to show a different amount.

    The only way round it otherwise is to store the breaks and rates in a table with a 'from' date and the calculation will used the document date to determine which rate and break to use. This latter method has the benefit of being able to be easily adapted for modelling - e.g. what would my turnover have looked like if I had used this break or that rate? and better documentation for what is happening in the business - we changed our discount structure on this date, the price of this product was changed on that day, etc and once set up, much easier to maintain. But more complex to develop in the first palce

    All depends on what you are doing with the db

  5. #5
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2011
    Posts
    62

    Updating Discount Rate... won't update the correct record

    Hey,

    Thanks for the advice above. I still have one more issue though.

    In the end, for my own specific needs, I just created a field entitled [DiscountRate] in tblInvoice. Here's the table:

    Click image for larger version. 

Name:	tblInvoice.JPG 
Views:	21 
Size:	18.5 KB 
ID:	31394


    From form Invoice, I easily calculate the discount rate, via my own expression. It appears on the form without any issues.

    However, when I run an update query to update the table tblInvoice with the newly calculated DiscountRate, it only updates the first record in the table ... not the current record.

    Here's an image of the update query:

    Click image for larger version. 

Name:	qupdDiscountRate.JPG 
Views:	21 
Size:	34.4 KB 
ID:	31395


    A macro runs the query on the close event of the invoice form...but when I look back at tblInvoice, it's only the first record that has the discount rate updated.

    Click image for larger version. 

Name:	tblINvoiceUpdated.JPG 
Views:	21 
Size:	26.0 KB 
ID:	31396



    Could the problem be that I'm running the query macro on the CLOSE event of the form, which is incorrect?


    PS: I've done something similar to this before which worked fine. (Updating Stock Quantities each time I invoiced out an item of stock)

    thanks

    Tim

  6. #6
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2011
    Posts
    62
    Since above post, I created a macro that opens form frmInvoice in 'Add' mode...which means that the proper InvoiceID will be updated, since only one record is 'current' on the form. Update query picks this up no problem.

    Only problem now is that if I view form frmInvoice in edit mode...i.e. toggle through all the invoice records, it still runs the macro to update discount amounts.

    So, new question is... how can I invoke a Macro ONLY if the form is opened in data mode? I thought I could do the following (see below) ...but that doesn't seem to help.

    Click image for larger version. 

Name:	Macro Data Mode feature.JPG 
Views:	21 
Size:	16.0 KB 
ID:	31397


    Thanks

    tim
    Last edited by tim_tims33; 11-23-2017 at 08:32 AM.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Hi Tim,

    Apologies for not responding - I've been moving house and my provider ceased the broadband way too early. Only just got back online.

    Have you resolved your issue?

  8. #8
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2011
    Posts
    62
    Yes, thanks Ajax. In an unexpected way, my last post is no longer an issue.

    Setting the Data Mode to Add, for the macro-query, doesn't seem to update table values when I open up
    Form in read only mode (eventhough update query warnings appear on screen)

    Best

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't use macros cannot help with that.

    I'm not too clear on what you are trying to do, but it sounds like you need to use the before insert event rather than the close event

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    A remark about discount calculating.

    How exactly do you calculate it?
    Code:
    if InvoiceTotal is > £200, then 5% discount, >100, then 2.5 % discount, else, no discount
    Does this mean that when invoice sum is 200, then the customer will pay 200 - 0.025*200 = 195.00, and when invoice sum is 201, then the customer will pay 201 - 0.05*201 = 190.95 (what is 4.05 less than 1.00 cheaper invoice)
    ,or when invoice sum is 200, then the customer will pay 200 - 0.025*(200-100) = 197.50, and when invoice sum is 201, then the customer will pay 201 - (0.025*(200-100) +0.05*(201-200)) = 198.45 (what is 0.95 more than 1.00 cheaper invoice).

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I still haven't seen where the OP is using/storing amount or total in order to calculate discount.

  12. #12
    Join Date
    Apr 2017
    Posts
    1,673
    As you don't have any price field in your example, I assume you calculate the (monetary) amount for every order row and the summary amount for order, using some StockPrices table. Do you have some unbound controls on form to display them? And how do you control, when those amounts are calculated.

    To calculate discount and final amount in orders form you have to use exactly same approach. In Orders table you'll have a boolean or smallint field to determine, is the discount applied or not. In Orders form, the user checks (or leaves unchecked) a control bound to this boolean/smallint field. Depending on this field checked or unchecked, in unbound control is discount calculated (>0 when checked, 0 when unchecked). In another unbound control, the final amount (= order amount - discount) is calculated.

    There are different ways to calculate all those amounts in unbound controls - I would use DSum() and DLookup() formulas. NB! In continuous forms you have to refer to field values in tables, not to form controls, as control value is always the value in active record! And to decide yourself what values will Access use, have for controls names not exactly same as field (as Access uses by default).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-19-2015, 11:05 PM
  2. Replies: 2
    Last Post: 09-14-2015, 09:01 AM
  3. Replies: 9
    Last Post: 12-17-2014, 06:13 PM
  4. Replies: 3
    Last Post: 07-25-2012, 12:22 PM
  5. Invoice and Reciept System
    By Kudo in forum Access
    Replies: 6
    Last Post: 02-17-2012, 11:41 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums