Cross-reference Distance Chart In SQL Server 2008 Or Excel?

- 1 answer

I would like to cross-reference construct a distance chart similar to the one here (example is a road-distance cross-reference chart) and, ideally, store the data in SQL Server 2008 (preferably the Express version). It needs these properties / abilities

  • Every column has a corresponding row with the same name (ie. not misspelled like my example).
  • Changing the value at one Row-Column intersection would update the mirror intersection (Column-Row) or the mirror data could be ignored.
  • The distance-values would need to be end-user editable.
  • The end-user would need to be able to add, delete or rename a column/row pair.
  • The end-user needs to be able to sort the columns and have the rows move automatically.
  • There could be hundreds of pairs.
  • a look-up query needs to find a distance given a start & destination (Row & Column)

The distance chart is reasonably straightforward to implement in Excel. Considering this, am I better off...

  • Using Excel as the user editing UI and then updating an SQL 'thing' with the new data?
  • Using Excel as the data-source even if it means performance issues with querying the data?
  • Using an as-yet undiscovered stroke of genius detailed here in an answer?


Sure looks like an Excel application to me, start to end. (heh)

I can't imagine your users typing enough data in to make performance an issue. Excel will only take 32757 rows by ditto columns. If that's enough, I'd say you're golden.