Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2008
    Posts
    3

    Unanswered: Finding the closest match

    My client tests various materials and they get a report that lists the different elements that were found in their test sample. They do not know what the material is, so they want to be able to enter the amount of the different elements found and show the most likely material matches.

    My thought was to start with a table that listed all the materials and how much of each element is in each. The key would be the material name and the feilds would be each element.

    I would then create a query entering in the value that was tested for each element. It would then (some how) compare the tested value to the "standard" value and give the closest matches.

    I have the query started and got so far as to compare the variation each element, but I am now trying to average the varience of all the elements so I can show a percent match.

    Since my varience is a calculated field I am not sure how to use my results in another calculated field.

    My next question is - Is there an easier way to do this?

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    easier way: instead of multiple columns, each with a different element, have just one column for the element, a column for the element type, and multiple rows, one per element

    it's called normalization and it makes your SQL a lot simpler and more efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2008
    Posts
    3
    If I understand correctly, you are saying instead of having one table with materials and elements together, have a separate table with elements and combine them.
    Similar to Customers and orders?

    If done this way, how do I search to find the closest one?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, similar to orders and orderitems

    the orderitems table has one row per item on the order

    your table would have one row per element in the material
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2008
    Posts
    3
    Got ya.

    So, how do I search this? I have the feeling I am still going to have a complex query searching multiple fields.

Posting Permissions

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