Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Apr 2008
    Posts
    40

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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have a look at PIVOT in the help files
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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....

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *over the top slap of the forehead* D'oh! What was I thinking?
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ....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)

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You were thinking??

    (just kiddin' mate)



    Wouldn't there need to be two "name" type fields in order to produce such a grid? I mean you have one name and one value... how can this be made to do anything of the sort?

    EG,

    London, 50
    New York, 250

    I can see no sense in making a grid of such data.

    What could the numbers represent??

    It would at least make sense if you had

    London, New York, 250
    London, Melbourne, 350
    New York, London, 250
    etc.

    I don't understand the problem I guess

    have the query create a difference between the two values.
    Which TWO values are you talking about?

    I think the OP needs to provide REAL example data and sample output tbph.
    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

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I am assuming that the OP is not really thinking about distances between cities and just used it as an example of the look he wants. Otherwise you are right - he is getting into a real world of pain.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes... and that's why I really think that some real sample data and a manually made sample output is needed. Plus it doesn't hurt to know the real situation.
    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

  9. #9
    Join Date
    Apr 2008
    Posts
    40
    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...

  10. #10
    Join Date
    Apr 2008
    Posts
    40
    oh, and I don't have the data on me - will post later

    but a quick description - ~70 species (or entries), each one with their own unique 8-10 digit long integer

    sounds like you are all suggesting a sort of hack solution - I will see if excel will do it...

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by oompa_l
    ...first off, I love the interpretation of the nickname...ha
    Oompy, just FYI:
    OP = original poster
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I have no idea what is going on with your database structure but this is something I tested & works.
    Code:
    TRANSFORM Max(res.no_diff) AS MaxOfno_diff
    SELECT res.name_row
    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
    PIVOT res.name_col

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No-body is interpreting your nickname. OP = Original Poster or something like that.

    Can you supply some sample data and sample output?

    "hack solution"?
    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

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    BAH!, I HAVE to get used to refreshing the page before replying!! lol
    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

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Another way - simply because you hardly ever see this form of Theta join.

    Code:
    TRANSFORM nz(Max(res.no_diff), "-") AS MaxOfno_diff
    SELECT res.name_row
    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
    PIVOT res.name_col

Posting Permissions

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