Full Version: Crosstab Query With 3 Inner Joins?
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
udigold1
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:

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?

Thanks!
vtd
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.

udigold1
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,
vtd
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?

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.