Crosstab query with 3 inner joins

udigold1

New member
Local time
Today, 09:39
Joined
Dec 19, 2010
Messages
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
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!
 
Well, first proble is *QueryResult* has firlds not seen in the other tables so not sure how/where you got that data. However, I would looking at RIGHT and/or LEFT JOINS as opposed to INNER joins which will only show you data if it exists in all of the tables included in the query.

I would first first create a SELECT query that gives you the results you want and then turn it into a CROSSTAB query.
 

Users who are viewing this thread

Back
Top Bottom