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.
How do I inner join it proeprly so I can get the Query result?
Thanks!
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
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:
[FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]TRANSFORM Sum([Table4].DataVal) As SumOfQuantity[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2]
[/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]SELECT Table1.FundID, Table1.FundName [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]FROM table4_monthrep .... inner join[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515].... GROUP BY 0511.KupaID, tbl1_order.KupaDesc[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]ORDER BY 0511.KupaID[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515][FONT=Consolas][SIZE=2][COLOR=#a31515]PIVOT table2_typeid, table2_TypeName;"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
How do I inner join it proeprly so I can get the Query result?
Thanks!