Results 1 to 11 of 11
  1. #1
    pero2112 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Oct 2020
    Posts
    3

    Different prices for products for different customers

    Hello,



    I want to build database and without going in to too much technical depth i need advice. Been trying and testing for a while and still can not figure out what is the best solution. I am new to access, few months playing with it in free time. I see so many benefits of it but very little knowledge.

    This is the question, i have multiple customers which has their own specific prices for products. So each customer would have different price for same product. What would be the best solution to store data? I need to build few forms as well. But this start is what is worrying me. I have table with the customers, and then separate table with products and prices for each customer. Would that be good way to continue?

    Thanks!

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I'd probably have a CustomerProduct table that would hold

    Code:
    CustomerProductID         AutoNumber
    CustomerID                    Long Integer FK
    ProductID                      Long Integer FK
    CustProdPrice                 Currency
    Plus any other fields you might feel are required.

    HTH

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Maybe the fields PriceValidFrom and PriceValidTo?

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Pero2112,
    Can you tell us more -in plain English - about different prices for the same product for different customers?

    If customer X and Customer Y each buy 1 of Product P --how do you determine the price you charge?
    Is it always some percentage discount? I would expect it would be cumbersome to change prices and/or add new customers and/or increase your product inventory.

    You may want to analyze your requirement and see if there is some pattern or simplification/efficiency option.
    Good luck.

    You can review the Similar Threads at the bottom of the page for related questions/responses.

  5. #5
    pero2112 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Oct 2020
    Posts
    3
    Quote Originally Posted by orange View Post
    Pero2112,
    Can you tell us more -in plain English - about different prices for the same product for different customers?

    If customer X and Customer Y each buy 1 of Product P --how do you determine the price you charge?
    Is it always some percentage discount? I would expect it would be cumbersome to change prices and/or add new customers and/or increase your product inventory.

    You may want to analyze your requirement and see if there is some pattern or simplification/efficiency option.
    Good luck.

    You can review the Similar Threads at the bottom of the page for related questions/responses.
    Hello Orange,

    its specific situation where all customers negotiate prices with the owner. There can not be percentage discount, i know it would be better way to go but impossible with current situation. Basically they were doing everything manually. They did not have even prices stored anywhere before few months. They had everything in their head. Invoices are still written by hand every day, more then 100 customers daily before this covid 19 slowed it down. There is plenty of room for improving the company (technology) and that's where i fit in. So trying to find a way to make it all easier. I did read through those similar threads, still did not find what i need. Part of the problem is that i dont know to ask right question as well. Thanks for your help.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't see any other way aside from post 2. Personally, I don't see a need for from/to fields. If you want to know what they paid for something in the past, you just look at the invoices table. The qty and price they paid (price came from CustomerProduct) is easily found. Of course, it would be different if you wanted a reminder to review the price at some point, but the whole operation doesn't seem that sophisticated as opposed to by the seat of someone's pants so to speak. Still, you wouldn't need a 'from' field for that - just a review date field.

    The idea that a percentage field (which would simplify this) is out of the question seems bizarre. If the nominal price is $1 and customerA is getting it for 75 cents, he's getting a 25% discount. How can that be out of the question?? It would also tell me as the business owner how much I'm giving up, and the more you know about your business, the better it's chances of success.
    Last edited by Micron; 10-30-2020 at 01:27 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    peroPrices-davegri-v01.zip

    See if you can get started with above. It's a Many-to-many setup, as advised by others above.
    Here's the data entry form:
    Click image for larger version. 

Name:	frmSetCustomerPrice.png 
Views:	35 
Size:	19.5 KB 
ID:	43346

    And the MTM structure allows you to get to the data like this:

    Click image for larger version. 

Name:	rptProductPriceLiist.png 
Views:	36 
Size:	26.9 KB 
ID:	43347

    And like this:

    Click image for larger version. 

Name:	rptCustomerPrices.png 
Views:	37 
Size:	24.3 KB 
ID:	43348
    Last edited by davegri; 10-30-2020 at 09:47 PM. Reason: sp

  8. #8
    pero2112 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Oct 2020
    Posts
    3
    That's excellent guys, you gave me a direction in which I need to go. That is all I need at this stage. Probably will be stuck along the way very soon but at least I can move from this point where I am at the moment. Will be back soon with another question. Really appreciate it.

  9. #9
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    I think that there is a MTM table already: the Invoice-Details table.
    A query with the last prices of the sold products for a specific customer, that would be enough. For a new invoice to a specific customer, if doesn't exists a record until now for a desired product, get the price from the price list (automatically) as default price.That's reduce the data entering, and, the data entering is not a fun and productive procedure for a business.
    So, as they works, "everything" from their head, will pass to their DB without too much effort.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    pero2112,

    Unless this business has a limited number of clients and a limited number of Products, I think it has the potential to be a nightmare. Perhaps if you gave us more details and examples of why and how this business is so unique, then some options may be forthcoming from readers. Accesstos has suggested that you have a product price that you use as a default, but I don't think there is a "standard price for a product" in your set up. And that may be the heart of the issue. As I mentioned earlier, product prices (even when there is 1 price for the product) will change with time and this trend seems to be a fact across all time periods and all industries.

    In general it seems you will be monitoring, numProducts *numCustomers (CustomerProductPrices) + all changed Prices for Products sold to Customers(as Welshgasman suggested in post #2). This could get increasingly complex if you are also dealing with payments over time(credit)(and defaults with penalties) and/or expanding your Product list and Customers.

    More info would help, both for readers to understand the requirement in context and for your design analysis.

    Good luck.

    Note: You are not the first to ask about different negotiated prices per product per customer.
    Last edited by orange; 11-01-2020 at 06:25 AM. Reason: spelling and link

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    would also suggest your customer prices table needs an effective from date field.

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

Similar Threads

  1. Replies: 22
    Last Post: 06-04-2020, 08:31 PM
  2. Replies: 9
    Last Post: 05-06-2018, 09:43 PM
  3. Replies: 2
    Last Post: 09-23-2012, 10:20 AM
  4. Different Prices for diff customers
    By kerrin in forum Access
    Replies: 4
    Last Post: 12-31-2011, 07:50 PM
  5. sample template customers and products
    By declanfogarty in forum Access
    Replies: 1
    Last Post: 05-27-2011, 06:54 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