Hi access people of dbforums. Im in kind of a pickle here, and I hope you can point me in the right direction on this.
As a part of a project, Im trying to get a query to look up data in a crosstab matrix (I believe it's called). For the sake of simplicity, lets say this is the distance between cities in a given region.
What Im trying to get out, is the sum of a specific amount of the distances between these cities for given clients.
The matrix looks like this (note this is just pulled out of thin air as an example):
X A B C D E
A 1 2 3 4 5
B 2 1 8 9 6
C 3 8 1 4 5
D 4 9 4 1 0
E 5 6 5 0 1
The first line would in this case be the Field Names in Access.
And for a given client, who might have an interest in cities B & C & E, what is the total distance for him between these cities? Ideally, a query would spit out something like this:
B to C = 8
B to E = 6
C to E = 5
(Most likely it will spit out some duplicate values, but I can fix that. Im just looking for a way to, for instance, get access to display the values for B, C & E in row B, and avoid the values for A & D)
My access experience is unfortunately limited to more basic queries involving "flat" tables, and my attempts to get a query to base which Fields it includes based on a value has failed. Getting the sum of these values has been simple enough, the hard part for me is to avoid getting the uninteresting cities (A & D) included in the calculations.