Unanswered: help making travel-distance-like cross tab query
I have a table of entries each one with a name and an integer value. I would like to have the names in the query to occupy BOTH the row and column headers and have the query create a difference between the two values. This should like those grid of values in travel guides that show travel distances between two places with the identical list in column and row positions....
any ideas?? how do I distinguish between the two values when I build the expression??
Won't that require some pretty funky use of Pivot George? I'm struggling to come up with any sort of solution, and certainly nothing that does not use lots of dynamic SQL - perhaps you are ahead of me.
This sort of thing (a matrix) is more suited to Excel et al than a set based language. I think I might even be tempted (horror of horrors) to consider using a recordset....
....or perhaps: a derived table containing a cross join of the table with itself, deducting values from each other, with a pivot operating on the derived table?
OP - some sample data please
Also - is this data stored in JET or somewhere else, such as SQL Server? How many rows in total? (because this is the sort of problem which requires processing power exponential to the number of rows being processed)
thanks everyone...first off, I love the interpretation of the nickname...ha
you are quite right about the travel distances - it was just the first thing that came to mind. Basically, what I am really after is a way to assign values automatically to establish 'closeness' which (i am hoping) I could feed into XML or graphML so that "edges" (relationships) between "nodes" are differentiated. I am hoping that I can say that the smaller the difference between the two numbers is, the closer the two objects should be when visualised.
If you want me to be even more specific, each of the entities is an animal and their value is achieved by stringing together numbers from separate tables - for this I assigned numbers, but I was hoping to use the table IDs themselves - which I think would have been pretty elegant.
Bottom line is that I need to 'describe' the relationship between each species and every other species - and for this I thought having the full list as column and row headers and differencing the two - then making the result positive no matter what - would always give me this 'degree of closeness'....
I am happy to explain further if it will help...if you have any other idea how to do what I am after I'm open to anything right now...
I have no idea what is going on with your database structure but this is something I tested & works.
TRANSFORM Max(res.no_diff) AS MaxOfno_diff
FROM (SELECT a.the_name AS name_row, b.the_name AS name_col, switch(a.the_name = b.the_name, "-", 1=1, a.the_no - b.the_no) AS no_diff from the_table AS a, the_table AS b) AS res
GROUP BY res.name_row
Another way - simply because you hardly ever see this form of Theta join.
TRANSFORM nz(Max(res.no_diff), "-") AS MaxOfno_diff
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