Page 3 of 9 FirstFirst 123456789 LastLast
Results 31 to 45 of 123
  1. #31
    leeli67 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    London UK
    Posts
    77
    I thought I could manually change the discount amounts in the tblCustomerDiscount, but maybe not.?

  2. #32
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You could but any older data would reflect the current discount which will mess up your accounting. So, if you do not want to keep a history of the discounts over time, you will have to store the discount at the time of the order (just like you currently store the price of the item). If that is how you want to proceed then I will add the code to do that in the database you posted the other day. Just let me know.

  3. #33
    leeli67 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    London UK
    Posts
    77
    yes please that would be most helpful.

  4. #34
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I've added the discount field to the order detail table and added the code in the after update event of the combo box in the subform. I also added the categoryID to the product combo box in the subform since the code needs that to find the discount. I also noticed that you used a query that included both the order detail table and the product table as the record source for the subform. This is incorrect. The subform should be based soley on the order detail table; I made the necessary corrections. The revised DB is attached.
    Attached Files Attached Files

  5. #35
    leeli67 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    London UK
    Posts
    77
    many thanks again , I will study the Database hard now and hopefully not have to bother you any more.

  6. #36
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You're welcome. Please do not hesitate to ask if you have further questions.

  7. #37
    leeli67 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    London UK
    Posts
    77
    just one question , should the price and discount fields in the subform be auto populating, or has that changed due to the subform now being based on the orderDetails table?, if so what's my best route to achieve this.

    also I noticed that the CustomerCatergoryDiscount is not present in the relationships. is that correct ?

  8. #38
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The price and discount fields should populate after you make a selection from the product combo box in the subform. For records that were already present (before my change), you will have to reselect the applicable product in the combo box.

    For the CustomerCatergoryDiscount table, I changed the name to CustomerCategoryDiscount (you had an extra r in the name). If it is not in the relationship diagram, go ahead and add it back in. (I did not go back and check that)

  9. #39
    leeli67 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    London UK
    Posts
    77
    yes your right after a few clicks its working,Prices are popping up now one thing the prices are rounding up or down too the nearest 1.00 or 2.00 and so on im guessing this would be something to do with the format or data type of the Price box.

  10. #40
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Yes, you need to change the datatype of the price field in the order detail table to currency

  11. #41
    leeli67 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    London UK
    Posts
    77
    Wow its really coming together now, feels like a working thing, Thank you so much for your help.

    Should I Work out the discount by way of a sum in the form or later when invoicing?

    I think I should work it all into a report

  12. #42
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You can actually do both. You could add a control in your subform, to calculate the total for each line item incorporating the discount (you would not store the total). You could also display the overall total for the order (see this site)

  13. #43
    leeli67 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    London UK
    Posts
    77
    Question to all now. JZWP11 has been a fantastic help and deserves a mention, Anyway I manged to work out something that works but dont know if im allowed to do it this way. It involves my calculation for working out line total for [Quantity] x [Price] x [Discount]/100 this formula gives me a line total which is the for example

    unit price £10 with a discount of 20% the calculation gave me a line total of £2 ? SO i changed the discount amount in the discount field to the remainder out of 100 I.e 80% instead of 20% and HEY PRESTO it returns the correct value in the line total field

    am i cheating .

  14. #44
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    That is the typical way of calculating a discount 1-(20/100)=0.80 or (100-20)/100=0.80 or 80% of the the original price

  15. #45
    leeli67 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Location
    London UK
    Posts
    77
    Hi JZW , I was wondering , what's the best way of using the Invoice table, in my DB as at the moment it is empty, I thought it might populate as I entered orders but no. I have been searching on sites and some people use a report to populate fields in the Invoice table, im not sure ? maybe a query first.

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

Similar Threads

  1. tricky trash can counting
    By M_Herb in forum Access
    Replies: 3
    Last Post: 02-16-2012, 10:42 AM
  2. Tricky (for me) SQL Query using COUNT
    By acdougla17 in forum Access
    Replies: 1
    Last Post: 10-31-2011, 01:49 PM
  3. Replies: 1
    Last Post: 08-11-2011, 12:48 PM
  4. SQL expression to perform a calculation
    By springboardjg in forum Queries
    Replies: 1
    Last Post: 05-20-2011, 06:57 AM
  5. Tricky Values in a Combo Box
    By vt800c in forum Forms
    Replies: 5
    Last Post: 05-19-2011, 01:33 PM

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