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 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...