Results 1 to 5 of 5
  1. #1
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46

    Different Pricing for different quantities - Normalization N

    Hello, I have hundreds of products with different prices.

    Seemed easy until I tried to normalize

    I do have the data in (ugggg) Spreadsheets Like this:
    example.zip

    Click image for larger version. 

Name:	excel.PNG 
Views:	15 
Size:	9.9 KB 
ID:	26199

    Note that this is only for TWO I have hundreds, the first thing is it would be good not to have to retype all that data, the second thing is the only way I can figure right now is to have a separate table for each items pricing structure? That just doesn't make sense....

    The pricing structure isn't a formula for quantities, from what I understand each product has different handling needs for the quantity.

    My sketches of this db look terrible, any good ideas or I will even take bad ones!


  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can import the data into access, but your excel file is missing one very important bit of data - a differentiator between each price break list. You would have one table

    tblPriceBreaks

    PriceBreakPK autonumber
    ProductFK long - link to a product table (this is the differentiator)
    PriceBreak long
    UnitPrice currency

    you may also need an effective from date field as well - presumably prices will change at some point

  3. #3
    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
    ?? How often do these change??

    If the PriceBreak is different for different Products, then I think the pricing structure has to be set up for each product.

    The samples you showed do not vary by a common percentage, so I think they are unique to each Product.
    What exactly did you try to Normalize??

    What is your intended use of Access?

  4. #4
    wrayman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    46
    You are correct different for each product.

    This is actually a lookup of vendors to get the pricing from distributors.

    I know there is going to have to be linked keys/fk.

    For Each Product Many Vendors
    For Each Vendor&Product Many Pricing Structures?

  5. #5
    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
    Is this a desktop application?
    First database?
    Have you mapped out the scope of the business that this database is suppose to support?

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

Similar Threads

  1. Auto Pricing
    By KH Ahmed Bara in forum Access
    Replies: 3
    Last Post: 12-23-2013, 09:13 AM
  2. Pricing Emails
    By Nibbles in forum Access
    Replies: 0
    Last Post: 08-20-2013, 12:54 AM
  3. Product Order/Pricing Database
    By atg in forum Database Design
    Replies: 3
    Last Post: 06-28-2012, 05:45 AM
  4. Pricing Problems
    By tony6562 in forum Access
    Replies: 1
    Last Post: 06-08-2012, 09:24 AM
  5. Pricing Database
    By nsvorp in forum Access
    Replies: 6
    Last Post: 09-10-2010, 10:33 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