Results 1 to 11 of 11
  1. #1
    ISUZU_ICE is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2018
    Posts
    5

    Best way for a sales DB with prices that don't change history


    Hi I am new,
    Nice forum.
    I want to make a basic small business database.
    Customers stay the same yet each customer has a different price, it also depends on product.
    Say one customer may be charged a different price for the same product as another.
    Yes prices are negotiated, supply and demand, quantity,, etc.
    So I need the historical prices to stay the same if customer negotiates a different price mid month.
    So when boss wants a statement it will reflect the prices at that time.
    Pretty much I do not want historical prices to change, lets face it if price changes mid month and the updated price updates older entries, it will be a useless monthly, yearly statement.

    Cheers

  2. #2
    ISUZU_ICE is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2018
    Posts
    5
    Also I smoke cigarette.
    Any tips on giving up much appreciated.
    Cheers

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You will need a table for customers with their name, etc. A table for products, with description and a default price. A third table to store product ID, customer ID, date, price. Then when you want to know the price at a certain time, you can check the dates.

    To give up smoking, make the decision - that is all. The same type of decision that stops you from robbing a bank - you don't do it!!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    ISUZU_ICE is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2018
    Posts
    5
    So for example the SalesDetails has to be entered manually every time you record a sale?
    I would like to have sales price calculated auto.

    aHH so the third table holds the current price all the time using the date?

    So for a statement I link the SalesDetails to customer price?

    Maybe I need four tables? As SalesDetails will have to be entered manually.
    Thanks

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by ISUZU_ICE View Post
    So for example the SalesDetails has to be entered manually every time you record a sale?
    At least you have to select article/product and customer (or p.e. product only, when sales are on subform of customers form).
    For sale date/date and time you can set default value for texbox control. Unless you want to change it p.e. when recording earlier sale, you don't need to edit it.
    For price you must decide, how you get it. principally there are 2 ways:
    a) You take last previous price for same product for same client as default price;
    b) You have previously negotiated the price with customer, and have it in some table (CustProdPriceID, CustomerID,ProductID, Price, ValifFrom), and default price is read from this table.

    The code for calculating the default price must be invoked, whenever sale date/datetime, customer, or product is changed on form. Depending how you calculate default price (variant a or b), you can edit the default price afterwards or not.

  7. #7
    ISUZU_ICE is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2018
    Posts
    5
    So I make a form to pull this data from 3 or more tables, and this will be a permanent record for a statement?
    The form makes a permanent table/record?

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    When the price is calculated from price history table (variant b), then usually it is not saved to sales table - whenever you need it, you calculate it. When the default price is calculated from sales table (variant a), you need to save actual sale price there too.

    At least you need
    Customers table;
    Products table;
    [Price History table];
    Sales table (in case you don't record sales orders, but handle sale of product as a single sale operation).

  9. #9
    ISUZU_ICE is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2018
    Posts
    5
    Could I have a combo/text box just to select price and have a range of prices in there?
    It may be simpler than dicking around trying to get the database to recognize the different price for each customer.
    OR
    What about CustomerTable, ProductPriceTable, and SalesTable with a calculated feild, If AND statement?

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    You can, but better you don't save the price in this case. Instead of calculated field in table, use unbound control with formula in form. In reports, you can use unbound control too, or in case you base the report on query, you calculate the price in query.
    No need for combo - when you have customer and product determined, and the passing price registered in [Price History Table], then a textbox with formula in form is enough - no user input is needed.

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    You can, but better you don't save the price in this case. Instead of calculated field in table, use unbound control with formula in form. In reports, you can use unbound control too, or in case you base the report on query, you calculate the price in query.

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

Similar Threads

  1. Replies: 4
    Last Post: 12-25-2016, 10:46 AM
  2. Replies: 5
    Last Post: 03-22-2016, 04:06 PM
  3. Replies: 2
    Last Post: 12-07-2014, 08:33 PM
  4. Replies: 2
    Last Post: 07-22-2014, 09:30 AM
  5. Replies: 1
    Last Post: 08-16-2011, 09:24 AM

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