Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Unanswered: Crosstab Query With 3 Inner Joins?

    Hi,

    I'm trying to make a crosstab query (with access tables), But I got lost writing the inner joins statements.
    My end result suppose to be the "QueryResult".

    Table1 holds the fund information,
    Table2 are the type of data the funds have
    Table3 is a conversion from the codes of the data to the type data in table2, and Table4 holds the data.

    Code:
    Table1
    FundID FundName
    1      Fund1
    2      Fund2
    3      Fund3
    4      Fund4
    5      Fund5
    6      Fund6
    7      Fund7
    
    Table2
    TypeID TypeName
    1      Balance
    2      Yield
    3      Fees
    4      Deposits
    5      Withdraws
    
    
    Table3
    CodeID TypeID    
    KT111   1
    KT112   2
    KT113   3
    KT115   3
    KT116   4
    KT117   4
    KT118   5
    KT119   5
    
    Table 4
    CodeID FundID DataVal
    KT111  1      1000
    KT116  2      40
    KT118  3      30
    KT119  3      30
    KT118  2      10
    KT119  2      50
    
    QueryResult
    FundID  Balance Yield Fees Deposits Withdraws
    1        1,000   1.5   555   40     60
    2        3,000   1.0   155   20     60
    3        2,000   0.5   255   70     60
    
    
    here's the partial sql statment:
    
    TRANSFORM Sum([Table4].DataVal) As SumOfQuantity
    SELECT Table1.FundID, Table1.FundName
    FROM table4_monthrep .... inner join
    .... GROUP BY 0511.KupaID, tbl1_order.KupaDesc
    ORDER BY 0511.KupaID
    PIVOT table2_typeid, table2_TypeName;
    "
    How do I inner join it properly so I can get the Query result?

    Thanks!

    P.S Table4 holds much rows then shown here.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    What do is take a couple of hits to get what I want

    Ie

    Query1

    Query2 reads query1

    Then I do the cross tab thing base on query2.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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