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:

TRANSFORM Sum([Table4].DataVal) As SumOfQuantity

SELECT Table1.FundID, Table1.FundName

FROM table4_monthrep .... inner join

.... GROUP BY 0511.KupaID, tbl1_order.KupaDesc

Order#39;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.

Thanks!

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#39;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:

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