Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Thanked 0 Times in 0 Posts

    complicated price structure (2000 SP3)

    I have a list of prducts purchased (roughly 10,000) with a very complicated pricing structure. Depending on a number of factors (material, size colour etc), prices can be at a set amount or as a discount off retail. I have set up appropriate tables and linkages to have Access look up the appropriate prices depending on code numbers which include codes for the material, size colour etc and this is working Ok but has become very cumbersome. eg in the fixed rate table I have to have a record for all possible permutations of material, colour and size.
    The actual price matrix from the supplier appears more simple: eg product type x, colour Y and materialz, all items with a size from 2mm to 30mm will be priced at 2.7 wheras all with a size from 35mm to 55m will be at 2.9, size 60 to 100 at 3.2 etc. Not every size is available for every product but I have no way of knowing in advance which sizes are available nor if they are going to produce a new product size (but still within the ranges specified above).
    Apart from having a table with all possible permutations, is there a smarter way to set this up.
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: complicated price structure (2000 SP3)

    You could create a price table like this:

    <table border=1><td align=center>Product</td><td align=center>Color</td><td align=center>Material</td><td align=center>SizeFrom</td><td align=center>SizeTo</td><td align=center>Price</td><td>Widget</td><td>Red</td><td>Iron</td><td>-</td><td align=right>9</td><td align=right>0.90</td><td>Widget</td><td>Red</td><td>Iron</td><td align=right>10</td><td align=right>34</td><td align=right>1.70</td><td>Widget</td><td>Red</td><td>Iron</td><td align=right>35</td><td align=right>59</td><td align=right>2.90</td></table>
    In a query, add this table and the order table or whatever. Join them on Product, Color and Material, and in the column for the size of the product, enter as criteria

    Between [SizeFrom] And [SizeTo]

    You can add the price from the pricing table and perform further calculations with it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts