Results 1 to 13 of 13
  1. #1
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43

    Unhappy Price List Structure - Numerous Attributes for Each Product

    Hello All,

    I’m plugging away at a sales DB I have created. Supper excited that it working as planned (mostly).

    I used the Sales Pipeline as the foundation and modified it significantly to my team’s requirements. Throughout this process, this forum has been invaluable, I’ve learned a lot. Thank you!

    I now would like your advice on how to create a price list that would be used to create quotes from this DB. I wish my price list was easy as 1 model code and 1 price; however, it is not the case. I apologize in advance for the lengthy write-up.

    Current Price List:


    PDF/Excel based list consisting of roughly 25 products.
    Each product has many features that can be added to the base model code.
    The longest model code could have upwards of 25 features that are individually priced and added to the base model price.
    I thought about simply adding each model code as it is quoted and recording this in a table but this would result in thousands of combinations and grow the database unnecessarily.

    Desired Outcome:
    I would like the user to be able to type a model code and the system perform a search and find the appropriate model code and price.

    Things I am considering:
    The base model code would be stored in 1 table while all additional features would be in an attributes table. This would be a 1 to many relationship, so 1 base model code could have many attributes.

    This format would be setup for all products, meaning, 2 tables, 1 containing all base model codes while the other contains all attributes.

    Feedback:
    Do you think my database structure is correct?
    How would I implement this in a search to find the appropriate match (starting with the base model code and then finding all related attributes and ‘tagging’ them 1 at a time to each other)?

    I suspect this will require fairly sophisticated VBA code…I’m becoming concerned whether I’m biting off more than I can chew!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You sound like you're on the right track, assuming my interpretation of the situation is correct. As for the number of records, thousands isn't a concern - 2GB for the entire db or one table is the limit (including overhead). That's a lot of text. What's not explained is whether or not each successive option is dependent upon one and only one parent or preceding option. If it is, then it's probably not too onerous. If it's not, then the number of permutations will be near astronomical. That is
    Base + opt1
    Base + opt1 + opt2
    Base + opt1 + opt2 + opt3
    This is 26 variations, including the base alone.

    Base + opt1
    Base + opt1 + opt2
    ....
    Base + opt2 + opt4 + opt11
    This will be a huge number. My math is rusty, but I think it is 25*24*23...*1+ the base. And that's just one model.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Quote Originally Posted by Micron View Post
    You sound like you're on the right track, assuming my interpretation of the situation is correct. As for the number of records, thousands isn't a concern - 2GB for the entire db or one table is the limit (including overhead). That's a lot of text. What's not explained is whether or not each successive option is dependent upon one and only one parent or preceding option. If it is, then it's probably not too onerous. If it's not, then the number of permutations will be near astronomical. That is
    Base + opt1
    Base + opt1 + opt2
    Base + opt1 + opt2 + opt3
    This is 26 variations, including the base alone.

    Base + opt1
    Base + opt1 + opt2
    ....
    Base + opt2 + opt4 + opt11
    This will be a huge number. My math is rusty, but I think it is 25*24*23...*1+ the base. And that's just one model.

    I think I follow you, but my math may be slightly different.

    25 products, each product has up to 10 possible primary options (length, cable, size, certification...), each primary option may have up to 10 (max) secondary options (length of 1m, 2m, 3m etc).
    So wouldnt this be 25 x 10 x 10 = 2,500?

    Each successive feature is dependent on each product (parent) and on primary/secondary options.


    (for size comparison, I looked at another database (excel data imported into a simple table). It contains 350,000 records and is about 250mb).

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I thought there were 25 options for each of 25 products. Not sure why.
    As for the rest, I still don't grasp the options, I guess. You say "10 possible primary options" but by using the word "possible" I can't tell what's required - all 10 or some of the 10. If there are 10 base options that have to be chosen, then that is one item/possibility/record. If some but not all can be chosen, then it is some other number, based on Combinations, Permutations or it's factorial (which is what I was getting at although it wouldn't start at 25) or it's some other number. If each one of those possibilities has 10 more possibilities, it is one thing if it applies to that 'one' record previously mentioned . I think it's an entirely different matter if each one of 10 base options has anywhere from 0 to 10 options and they're not dependent on each other.
    Using your example with 3 products, 3 primary and 3 secondary options it should be 3x3x3 = 27. Consider the following

    Prod Popt Sopt
    A 1 X
    B 2 Y
    C 3 Z

    If I've done the relationships correct (matches your situation) I get 36 possibilites
    A1
    A2
    A3
    A1X
    A2X
    A3X
    A1Y
    A2Y
    A3Y
    A1Z
    A2Z
    A3Z
    B1
    B2
    B3
    B1X
    B2X
    B3X
    B1Y
    B2Y
    B3Y
    B1Z
    B2Z
    B3Z
    C1
    C2
    C3
    C1X
    C2X
    C3X
    C1Y
    C2Y
    C3Y
    C1Z
    C2Z
    C3Z

    Imagine the extra combinations when dealing with 25, 10 and 10. Ok, so if my understanding of the combinations isn't correct, then it doesn't really matter. May not matter even if I do. The important thing is whether or not your design question was answered since it's unlikely that every possible combination would ever be ordered. People are not likely to order every possible combination under the sun.

    As for the other db size, there are factors which can influence the file size between different db's with the same number of records so it might give you some idea of what you'll end up with, but it doesn't mean that 700,000 records will be 500Mb.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    I advice to shift your product base level a step.

    Product/Part/Article/whatever table is where you register products you sell. You set the price for product. Every product has fixed set of options, which may be used to calculate product's price.

    When you need, you can have a ProductGroup table (like your current products table). Products table will then have a FK field to store ProductGrop to link the product to specific product group.

    Then you have ProductOptions table, which has FK field to store product PK value (link to products table), a field to store option type (length, cable, size, certification, ... - it may be an integer field for option id, with options in separate table), a field for option value type (text or numeric) (or you store the value type in options table), and 2 fields for option values (one for text values, another for numeric values), .... On form, only one value field is visible for record - depending on value type. ProductOptions table is used mostly, when product price is calculated and options must be taken into account.

    In products table, you'll have a field (ProductName/ProductCode/...) which must be unique, and must determine all options needed for identification of product by user. This field will be visible field in all combo boxes for selecting the group in forms. NB! Probably not all options must be included. From your example, the length of product must be included, but I suspect there is no products with all other options same, but having several different certifications or different certificate sets. So the ProductName field may have value like "ProductGroup: L=3m, W=1m, H=2m, Cbl RS"

  6. #6
    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
    You might get some ideas from these links.
    https://www.accessforums.net/showthr...uct+attributes
    https://dba.stackexchange.com/questi...base-structure
    https://access-programmers.co.uk/for...d.php?t=289459

    You mention price, but in reality you may have sales and/or loyalty programs where multiple prices based on some condition(s) may apply. You also haven't mentioned if there is a need to manage/monitor/report price history(s)(that is changes with time).

    Good luck with your project.

  7. #7
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Quote Originally Posted by Micron View Post
    I thought there were 25 options for each of 25 products. Not sure why.
    As for the rest, I still don't grasp the options, I guess. You say "10 possible primary options" but by using the word "possible" I can't tell what's required - all 10 or some of the 10. If there are 10 base options that have to be chosen, then that is one item/possibility/record. If some but not all can be chosen, then it is some other number, based on Combinations, Permutations or it's factorial (which is what I was getting at although it wouldn't start at 25) or it's some other number. If each one of those possibilities has 10 more possibilities, it is one thing if it applies to that 'one' record previously mentioned . I think it's an entirely different matter if each one of 10 base options has anywhere from 0 to 10 options and they're not dependent on each other.
    Using your example with 3 products, 3 primary and 3 secondary options it should be 3x3x3 = 27. Consider the following

    Prod Popt Sopt
    A 1 X
    B 2 Y
    C 3 Z

    If I've done the relationships correct (matches your situation) I get 36 possibilites
    A1
    A2
    A3
    A1X
    A2X
    A3X
    A1Y
    A2Y
    A3Y
    A1Z
    A2Z
    A3Z
    B1
    B2
    B3
    B1X
    B2X
    B3X
    B1Y
    B2Y
    B3Y
    B1Z
    B2Z
    B3Z
    C1
    C2
    C3
    C1X
    C2X
    C3X
    C1Y
    C2Y
    C3Y
    C1Z
    C2Z
    C3Z

    Imagine the extra combinations when dealing with 25, 10 and 10. Ok, so if my understanding of the combinations isn't correct, then it doesn't really matter. May not matter even if I do. The important thing is whether or not your design question was answered since it's unlikely that every possible combination would ever be ordered. People are not likely to order every possible combination under the sun.

    As for the other db size, there are factors which can influence the file size between different db's with the same number of records so it might give you some idea of what you'll end up with, but it doesn't mean that 700,000 records will be 500Mb.
    This is exactly the structure that I am dealing with. So to clarify, there are 25 products. Each can have up to 25 base model codes (codes to which additional options can be added). The additional options can max-out at around 25. Of the 600+ base model codes, about 50 will have additional options that number in the 25 range.

  8. #8
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Quote Originally Posted by ArviLaanemets View Post
    I advice to shift your product base level a step.

    Product/Part/Article/whatever table is where you register products you sell. You set the price for product. Every product has fixed set of options, which may be used to calculate product's price.

    When you need, you can have a ProductGroup table (like your current products table). Products table will then have a FK field to store ProductGrop to link the product to specific product group.

    Then you have ProductOptions table, which has FK field to store product PK value (link to products table), a field to store option type (length, cable, size, certification, ... - it may be an integer field for option id, with options in separate table), a field for option value type (text or numeric) (or you store the value type in options table), and 2 fields for option values (one for text values, another for numeric values), .... On form, only one value field is visible for record - depending on value type. ProductOptions table is used mostly, when product price is calculated and options must be taken into account.

    In products table, you'll have a field (ProductName/ProductCode/...) which must be unique, and must determine all options needed for identification of product by user. This field will be visible field in all combo boxes for selecting the group in forms. NB! Probably not all options must be included. From your example, the length of product must be included, but I suspect there is no products with all other options same, but having several different certifications or different certificate sets. So the ProductName field may have value like "ProductGroup: L=3m, W=1m, H=2m, Cbl RS"
    Thank you very much for this. I am now trying to understand all that you have suggested, I will be back for more questions. Regards,

  9. #9
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    To get your tables structure right, use a piece of paper and write out the formula (as you understand it) to manually calculate the process using the PDF/Excel sources. Each value used is a field, which will need a table to reside in.

    A couple of questions for you on this.
    1) Is the price for Opt1 always the same or does it change depending on the base? (I will assume the same).
    2) Is it possible to have many of 1 option together? (eg x feet of cable) (I will assume no)

    Assuming the answers above try:
    tblProducts ```` ProductPK ____ ProdDesc ____ Description ____ Price ____ etc.

    tblOptions ```` OptionPK ____ OptDesc ____ Description ____ Price ____ etc.

    tblProductOptions ```` ProductOptionID ____ ProductFK ____ OptionFK

    Now to add in question 1 you could add a Price/PriceFactor/PriceRatio field to tblProductOptions if that is consistent. Just remember everything should be a solid rule and not generally ....
    To handle question 2 it becomes a question on how the "model" is listed. If it is sold as Prod1Opt1Opt2Opt3 then one of those options is the length of cable needed. If it is sold as Prod1Opt1xOpt2yOpt3z where xyz are multiples of each option, then you need an additional table closer to the customer (tblCustomProducts ?) (Maybe that is where the price goes?)

    I hope I have understood your question correctly and have helped you some way.

  10. #10
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Thank you for the links. I read the material and may be able to use some concepts.

  11. #11
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Quote Originally Posted by Western_Neil View Post
    To get your tables structure right, use a piece of paper and write out the formula (as you understand it) to manually calculate the process using the PDF/Excel sources. Each value used is a field, which will need a table to reside in.

    A couple of questions for you on this.
    1) Is the price for Opt1 always the same or does it change depending on the base? (I will assume the same).
    2) Is it possible to have many of 1 option together? (eg x feet of cable) (I will assume no)

    Assuming the answers above try:
    tblProducts ```` ProductPK ____ ProdDesc ____ Description ____ Price ____ etc.

    tblOptions ```` OptionPK ____ OptDesc ____ Description ____ Price ____ etc.

    tblProductOptions ```` ProductOptionID ____ ProductFK ____ OptionFK

    Now to add in question 1 you could add a Price/PriceFactor/PriceRatio field to tblProductOptions if that is consistent. Just remember everything should be a solid rule and not generally ....
    To handle question 2 it becomes a question on how the "model" is listed. If it is sold as Prod1Opt1Opt2Opt3 then one of those options is the length of cable needed. If it is sold as Prod1Opt1xOpt2yOpt3z where xyz are multiples of each option, then you need an additional table closer to the customer (tblCustomProducts ?) (Maybe that is where the price goes?)

    I hope I have understood your question correctly and have helped you some way.

    Hi Neil,

    Yes, certainly, your answer has brought me closer to my objective. I examined your response closely and tried to build a simple DB using what I have learned. Attached is the relationship window of this sample system.

    To answer your questions,
    1. Is the price for Opt1 always the same based on the base? A. Yes, the price of the base
    2. No, it is not possible to have many of 1 option. The option can only be selected once (e.g. system length cannot be customized due to electrical engineering principles).

    How would you organize this in a form to search for the required product code + all options? Would you use multiple combo boxes? Would you write a VBA code to search each table based on the model code entered (truthfully, I have no idea how to do this, but I'll need to learn)?Click image for larger version. 

Name:	Capture.PNG 
Views:	26 
Size:	34.2 KB 
ID:	34476

  12. #12
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    The model looks okay, but without data it hard to say for sure. What I do (caution here) is start filling in some data into a spreadsheet and write a formula to create outputs for ECAttributes. This confirms that you have all the pieces needed. Remember you are testing, look for length, uniqueness, strange ones to test, not volume. The odd ball ones (oh by the way ones) don't need code yet, but should be able to be stored and recreated. While doing this look for duplicates and repeating clumps which may mean you need a new table or two.

    Your Primary Keys (PK) being labeled the same will cause your code (and debugging) to become hard to understand. Read up on naming conventions, it will help later. (PS I see other naming issues, but each to his own.)

    You started with "Sales Pipeline" example, I'm sure it has a querry in it (if not find one). So plug in some data (or import), remember this a test, into your DB and using that querry modify it. Play with it, understand what happens, oh by the way, are all the pieces there (somewhere)? The order and structure of the output will come latter.

    The next step is Import data OR write a Report/Output screen OR create an Input Screen OR what ever you really need now. Just do as little as possible to fill the need and then work something else. (It's like laying bricks, little pieces make big things, the model is the foundation). Test each piece, and if you change an existing piece you already use, test those pieces again. Errors, mistakes, missings, and oops are common, they are easier to correct now (with the code is fresh in your mind) than latter (don't have a clue what this is doing).

    As you work and play with this, it will "tell" you what is needed (pull down, combo, prefilled) in each screen. Just remember, you know what it wants and means, your co-worker doesn't.

    Have Fun

  13. #13
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Quote Originally Posted by Western_Neil View Post
    The model looks okay, but without data it hard to say for sure. What I do (caution here) is start filling in some data into a spreadsheet and write a formula to create outputs for ECAttributes. This confirms that you have all the pieces needed. Remember you are testing, look for length, uniqueness, strange ones to test, not volume. The odd ball ones (oh by the way ones) don't need code yet, but should be able to be stored and recreated. While doing this look for duplicates and repeating clumps which may mean you need a new table or two.

    Your Primary Keys (PK) being labeled the same will cause your code (and debugging) to become hard to understand. Read up on naming conventions, it will help later. (PS I see other naming issues, but each to his own.)

    You started with "Sales Pipeline" example, I'm sure it has a querry in it (if not find one). So plug in some data (or import), remember this a test, into your DB and using that querry modify it. Play with it, understand what happens, oh by the way, are all the pieces there (somewhere)? The order and structure of the output will come latter.

    The next step is Import data OR write a Report/Output screen OR create an Input Screen OR what ever you really need now. Just do as little as possible to fill the need and then work something else. (It's like laying bricks, little pieces make big things, the model is the foundation). Test each piece, and if you change an existing piece you already use, test those pieces again. Errors, mistakes, missings, and oops are common, they are easier to correct now (with the code is fresh in your mind) than latter (don't have a clue what this is doing).

    As you work and play with this, it will "tell" you what is needed (pull down, combo, prefilled) in each screen. Just remember, you know what it wants and means, your co-worker doesn't.

    Have Fun
    Hi Neil, having made significant progress on this "small" database, I see and appreciate your way of guiding me. In my current database, I have had to make several changes (structurally) because of a lack of experience in how to design these things and because I did not do as you suggested, deeply think about things before diving in. I will dig deeper and come back to you. Thank you

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

Similar Threads

  1. Price Calculation base upon product age
    By streub in forum Access
    Replies: 6
    Last Post: 11-13-2017, 09:31 AM
  2. Replies: 1
    Last Post: 11-01-2017, 04:37 PM
  3. Last purchase price of a product sale
    By EMAS in forum Queries
    Replies: 4
    Last Post: 12-22-2015, 02:01 PM
  4. Replies: 4
    Last Post: 04-26-2013, 08:32 AM
  5. Replies: 1
    Last Post: 10-06-2009, 02:00 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