Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2008

    Unanswered: calculating differences between entities

    this is sort of a continuation of a previous thread where I sought help to make a "table of cross tab travel distances". The application was to assign values to the amount that two entities differed, in my case my entities represented animals. Each animal was assigned various attributes refered to in a number of tables, like habitat, diet, weight etc etc. Each entry in each of those table was given a value which once strung together, would produce code that represented the animal more or less. When you subtract each animal from every other animal you would get a number describing similarity. I needed these numbers to both give weights to a Force Directed Graph Layout and also to weed out those animals which just aren't related (those who do not share any attributes).

    I got alot of help from forum members and things were working just fine - BUT I need to go back and reformat things. Instead of a table where the first column and row represent are headings with the entity names, I need a table consisting of a source entity, target entity and weight. Now this info is already produced by the initial SQL that was given to me:

    SELECT res.name_row, res.name_col, nz(Max([res].[no_diff]),"-") AS MaxOfno_diff INTO differenceValues
    FROM (SELECT a.the_name AS name_row, b.the_name AS name_col, a.the_no-b.the_no AS no_diff FROM the_table AS a INNER JOIN the_table AS b ON a.the_name<>b.the_name) AS res
    GROUP BY res.name_row, res.name_col;

    but there are some things I need to do to this to avoid lots hand sorting...:

    1- I need to remove duplicate entries. What I mean by this is that I don't want a row connecting two entities followed somewhere else by another row with the same entities just switched source and target...

    2- It would be ideal to just filter out or delete those rows where there is no "similarity" between species.

    are either of these possible? I can help explain further...

    thanks yet again!

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    I think you should be using a cross join here given your inequality operator as the join condition. You're effectively after a Cartesian product if I'm reading that correctly...

    As far as filtering, you can add a "HAVING NZ(MAX([res].[no_diff)) > SomeThresholdGoesHere" assuming MaxOfno_diff is the metric you're using to decide what is or is not similar.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2008
    Thanks Teddy. I don't actually know how to read SQL, I don't even know what you mean when you say a Cartesian product. I can sort of make out that the inequality is that the animals are different -yes !
    as far as the second line goes, I am wondering if it's possible to force the values into a new format...something like 00-00-00 for three different points of comparison
    and then in the HAVING search for those with 00 condition...I was doing all this sort of stuff manually before in excel in order to do the weeding. I used the above mentioned format to locate those relationships which had no 00s and deleted them...
    Last edited by oompa_l; 08-05-08 at 19:56.

Posting Permissions

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