Results 1 to 9 of 9
  1. #1
    GreatOffender is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    4

    Query Finding Cloest Value

    Sorry if it's a bit heavy for a nebie's first post but I have an order form that calculates the cost based on a price grid. Let's just say it's bulk t-shirts that can be purchased based on how many colors. The first column is the quantity with the other column headers being the number of colors. It's easy, for me, to calculate the price if I order exact quantities but what if I ordered 225, 3 color shirts. Is there a way to lookup the corresponding value when it's not exact?

    On the order form the customer would but in his order quantity and then select the # of colors. I need the price to be based on the 200 row, 3 color column. I can do it if it's an even 200 because it becomes a nice simple query. I need close if it can be done. If I'm not making sense then shout it out and I'll try to be more specific.

    225 shirts (3 color) @ $18.00.

    Thanks for looking.



    Qty 1 2 3 4
    100 $15.00 $17.00 $19.00 $21.00
    200 $14.00 $16.00 $18.00 $20.00
    300 $13.00 $15.00 $17.00 $19.00
    400 $12.00 $14.00 $16.00 $18.00
    500 $11.00 $13.00 $15.00 $17.00
    600 $10.00 $12.00 $14.00 $16.00
    700 $9.00 $11.00 $13.00 $15.00
    800 $8.00 $10.00 $12.00 $14.00
    900 $7.00 $9.00 $11.00 $13.00
    1000 $6.00 $8.00 $10.00 $12.00
    Last edited by GreatOffender; 11-12-2018 at 04:54 PM. Reason: Edited for clarity

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Closest level, or highest level less than or equal to the quantity ordered (ie what price would they pay for 275)?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    A couple of other thoughts. What if they order less than 100? Your design is not normalized, it should be more like

    Quantity...Colors...Price
    100 1 15
    100 2 17

    your design is more like a spreadsheet (short and wide), databases are stored long and narrow. Think about it this way. If they decide to offer 5 colors, everything in your database has to be redesigned.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    GreatOffender is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    4
    Sorry I didn't get back to you sooner but work gets in the way. You are correct that this is "like" a spreadsheet because it mirrors a price grid from a spreadsheet. I will try changing the layout but my observation thus far is that Access takes 100% more effort than excel. The goal of this project is to remove Excel and move to Access so the result can be used by my dept. but I guess I am too used to the speed I can work in Exel. I'm stubborn and I do have more questions but I will give your suggestion a try.

    Thanks for your input!

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem. It's common for people coming from a spreadsheet background to make these kinds of design mistakes when creating a database. I did it too. Databases require a different mindset, but designed properly will be more flexible and dynamic, and certainly perform very quickly.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    GreatOffender is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    4
    So I'm really going to have to play ignorant here so let me lay it out as the grids are used now. This was a sample and nothing is ever ordered below 1,000 with most orders being in the 10's and 100's of thousands. Depending on the client this could go into the millions. They are ordering paper and plastic products and usually do so by using forcasted needs during a 6 month period. It is not uncommon for a client to order 360K of a particular piece of collateral. The price grids are built by the 1,000's than 5K's then 10K's 100K's 500K, 750K, etc... with the # of colors never exceeding 4. Using Excel I could Index and Match this give me the price of 360K by dropping to the 100K row and matching the color column. By querying >= to 360K it shows me all of the values below 500K. I just need someone to fill in 360,000 in the order form and have the calculation (based on the Catalog#) show the cost for that quantity. Can this be done and will it take the time to lay it out as you have above? Thanks for the patience!

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You'd query for <= the order quantity, but use the TOP predicate with the appropriate sort to get the single correct record. Your criteria in a normalized database would include the color. If you keep the design you have with colors as fields, you'll need to come up with a way to get the appropriate value. It can be done, just a hoop that's not normally jumped through.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    GreatOffender is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2018
    Posts
    4
    Thanks for all of your help. I am reading 2 books simultaneously with a 3rd waiting for the completion of the first 2 but they all seem to gloss over aspects of how they arrived at certain conclusions and those questions are for another day.

    Again, Thank you!

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, it can be a lot to digest. Please post back if you're still stuck, and welcome to the site by the way!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Finding a Percent Query
    By pickslides in forum Queries
    Replies: 1
    Last Post: 10-21-2018, 10:30 PM
  2. Replies: 1
    Last Post: 05-22-2018, 07:47 AM
  3. Query finding sales by date
    By v!ctor in forum Queries
    Replies: 9
    Last Post: 10-14-2012, 04:41 PM
  4. Noob Finding Query
    By pmortlock in forum Queries
    Replies: 4
    Last Post: 01-06-2012, 09:04 AM
  5. Finding Median in Query
    By randolphoralph in forum Queries
    Replies: 1
    Last Post: 02-19-2010, 11:18 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