Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    6

    Question Unanswered: Need a formula to determine the lowest price

    I have3 vendor databases that have a manufcaturer part # as the common item.
    I am trying to determine a formula that displays the lowest price amongst the 3 and only the lowest price. here is an example:

    Headers
    Distributor 1 Part# Price
    JOE INC 12345 45.00
    SAM INC 12345 48.50
    JIM INC 12345 42.75

    So amongst these 3 I would want to only show the $42.75 and JIM as the distributor.
    I would like to do this in the builder in the query,. Any ideas help or suggestions are appreciated!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    merge the data into one table
    then run a query which identifies the lowest price

    eg
    select top 1 Distributor, aColumn, PartNo, Price from mytable
    where PartNo = 12345
    order by Price ASC

    if your PartNo is Alphabetic / String / Text then

    select top 1 Distributor, aColumn, PartNo, Price from mytable
    where PartNo = "12345"
    order by Price ASC

    if you want to do a partial match on a string / text
    select top 1 Distributor, aColumn, PartNo, Price from mytable
    where PartNo like "12345*"
    order by Price ASC
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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