My Assistant
Site Message
(Message will auto close in 2 seconds)
 Crosstab Query With 3 Inner Joins?
|
|
 Jul 10 2011, 03:07 PM
Post
#1
|
|
|
New Member Posts: 2  |
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 rawdata. CODE Table1 How do I inner join it proeprly so I can get the Query result?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 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;" Thanks! |
 |
 
|
 |
Replies
(1 - 3)
|
Jul 10 2011, 05:59 PM
Post
#2
|
|
|
Retired Moderator Posts: 19,667 |
Is that the consistent set of sample data you posted???
If so, you need to explain your logic why FundID 3 appearing in the Query results but not FundIDs 4-7. Also, we don't know where most of the values in the Query results come from??? A CrossTab Query can handle 3 Inner Joins. If you are not familiar with CrossTab Queries, suggest you create a normal Select Query that retrieve all the required data items than use this Select Query as the data source for the CrossTab. |
|
|
|
|
Jul 11 2011, 01:25 AM
Post
#3
|
|
|
New Member Posts: 2 |
Hi,
Table 4 holds much more rows, and I didn't want to write that many rows in the post because it would've made it cumbersome. All the Data comes from Table 4. Thanks, |
|
|
|
|
Jul 11 2011, 05:56 PM
Post
#4
|
|
|
Retired Moderator Posts: 19,667 |
1. If you post sample data set, you need to provide a consistent sample as we don't know your data and rely on the sample data to see what you want. Posting an inconsistent sample data set only confuses potential respondenta (evidenced by the number of responses to your question!).
If you don't want to type, then post your sample database (as a zipped file) 2. Have you tried constructing the Select Query first as per my last post? |
|
|
|
 |
 |
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 24th May 2013 - 11:29 PM |