Results 1 to 5 of 5
  1. #1
    blueman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2015
    Posts
    26

    Using and storing "Sale Price"

    I am trying to figure out the best way to store or use a Sale Price. I do want to and have already established that the Order Details Table stores a unit price as a permanent record (not calculated). In a previous version of the database (previous to what I'm working on now), the Unit Price was added at the time the Order was entered. I've since added Unit Prices to the Products table, so most of the time, I want the Orders Table to be able to get that price and put it in the Unit Price Field on the Orders Detail form so that it is a permanent record sincer the established Unit Price associated with each product in the Product Table can change. I would assume I can do that with some code in the After Update event of the combo box that selects the product on the Order Details form (subform).



    Aside from that, we have occasional Sales. The sales are usually a dollar amount and not usually a percentage so using a calculated control with percentage isn't going to work. I believe I will need a separate field in the Order Details Table. I'm not sure I can use it or should use it in the Products Table but maybe I'm wrong. There's then the issue of having a side by side field of Unit Price and Sale Price and how (if that's the way to go) to deal with the final calculations of the calculated controls given that the Unit Price field, may or may not be null. Anyway, I'm looking for some help in directing me to the best method for accomplishing this.

    Thanks for your help

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You would normally have a Price for an Item in the Item table. That would be currentPrice, you can change it when necessary.
    In the OrderDetail you would have AgreedToPrice or similar name that indicates the Price for that item for that Customer on that date - this could include salePrice, Discount, Loyalty Price etc. AgreedToPrice and Quantity are recorded within OrderDetail.

    If you simply refer to the price in the item table, you will lose historical data.
    You never store Totals in a table -- you calculate them when needed.

    You may find this video of some use. see post#6 at
    https://www.accessforums.net/forms/h...ory-42524.html

  3. #3
    blueman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2015
    Posts
    26
    Looked at the video, thank you, it makes sense and I think this is pretty much where I was heading and what I was suggesting. I may not have explained it well:

    You never store Totals in a table -- you calculate them when needed.
    Understood. The calculations that I was referring go into the totals and subtotals calculated fields which are not part of my tables.

    You would normally have a Price for an Item in the Item table. That would be currentPrice, you can change it when necessary
    I've since added Unit Prices to the Products table,]
    In the OrderDetail you would have AgreedToPrice or similar name that indicates the Price for that item for that Customer on that date - this could include salePrice, Discount, Loyalty Price etc. AgreedToPrice and Quantity are recorded within OrderDetail.
    I believe I will need a separate field in the Order Details Table. I'm not sure I can use it or should use it in the Products Table but maybe I'm wrong.
    So given that I may be on the right track with that, my other questions were:

    Given that I will have 2 fields; The Unit Price Field (taking it's value from the Products/Items Table) and a new Sale/Agreed Price, etc, what is the best way to handle the fact that the Unit Price will call the Unit Price even if there is a Sale Price established for that Transaction? How do I best negate the value in the Unit Price Field?

    Should I in fact, have an after update event on the Product Combo Box so when the product is suggested, it fills in the current Product Price from the Product table?. If so, do you have suggestion for that.?

    Thanks again for your help.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In the OrderDetails table have a field called AgreedToPrice, or SellingPrice or PricePaid along with a Quantity field.
    The quantity is where you record Number of Units Sold, and the AgreedtoPrice is the unit price you sold that item for to that Customer on this transaction. The AgreedToPrice can be taken from the Product Current Price, or it can be a "ClearanceSalePrice" or a price determined as part of a LoyaltyProgram or some other Price you happen to agree to -- it is recorded in OrderDetails and it is the Price you sold at.

    I think you may find that some sales will involve common Customers(loyalty) or a Sale price. The AgreedToPrice may or may not be the same as the CurrentPrice, but it is recorded in OrderDetails and will continue to exist after you change the CurrentPrice in the Product table.

    You could also have a table related the Producttable to indicate when a Price Started and Ended.
    There may be other ways to implement this

    Price
    PriceStartDate
    PriceEndDate

    and each record shows when the Price was effective. If you do this you could record ProductPrice in OrderDetails.

    I prefer the AgreedToPrice. If you want to know a Product's price history, you may have to adapt the PriceStarted/Ended option.

    Good luck
    Last edited by orange; 11-12-2015 at 03:56 PM.

  5. #5
    blueman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2015
    Posts
    26
    Thanks
    I'll play with that.

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

Similar Threads

  1. Replies: 4
    Last Post: 09-23-2015, 09:13 AM
  2. Replies: 5
    Last Post: 11-23-2014, 03:54 PM
  3. Replies: 22
    Last Post: 09-04-2014, 09:12 PM
  4. "Marking" the largest sale
    By snipe in forum Queries
    Replies: 7
    Last Post: 06-17-2014, 05:49 AM
  5. Replies: 1
    Last Post: 12-20-2013, 05:14 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