Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2008

    Unanswered: Looking up values in a crosstab table

    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.

    Thanks in advance

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    B to C = 8 -► SELECT B FROM QueryName WHERE X = "C"
    B to E = 6 -► SELECT B FROM QueryName WHERE X = "E"
    C to E = 5 -► SELECT C FROM QueryName WHERE X = "E"
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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