帮助使用 Access 创建交叉表查询

发布于 2024-11-19 19:06:10 字数 1252 浏览 2 评论 0原文

我正在尝试进行交叉表查询（使用访问表），但我在编写内部联接语句时迷失了。我的最终结果应该是“QueryResult”。

表1保存基金信息，表2是基金拥有的数据类型 Table3是将数据的代码转换为table2中的类型数据，Table4保存数据。

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
KT118  3      30
KT119  3      30
KT118  2      10
KT119  2      50
KT111  2      3000
KT111  3      2000
KT112  1      1.5
KT112  2      1.0
KT112  3      0.5

PS：Table4 包含很多行，然后在此处显示我不需要的代码。

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

获取查询结果的正确语句是什么？（我迷失了内部连接......）还有一种方法可以对某些数据求和，并显示 table4 中其他数据的值（不求和）吗？

谢谢！

原文

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 data.

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
KT118  3      30
KT119  3      30
KT118  2      10
KT119  2      50
KT111  2      3000
KT111  3      2000
KT112  1      1.5
KT112  2      1.0
KT112  3      0.5

P.S: Table4 holds much rows then shown here with codes which I do not need.

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

What is the right statement to get the query result? (I got lost on the inner joins...)
Is there also a way to sum some of the data, and show the value (without summing) of other data from table4?

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

挽手叙旧 2024-11-26 19:06:10

虽然我不太确定您的所有要求，但这可能会帮助您开始：

TRANSFORM Sum(d.dataval) as DataValue
SELECT d.fundid
FROM 
((Data d
INNER JOIN fund f ON d.fundid = f.fundid)
INNER JOIN  code c ON d.codeid = c.codeid)
INNER JOIN type t on c.typeid = t.typeid
GROUP BY d.fundid
PIVOT T.Typename

结果：

fundid  Balance Deposits    Withdraws   Yield
1       1000                            1.5
2       3000    40      60      1
3       2000            60      0.5

while I am not exactly sure of all of the requirements you have, this might get you started:

TRANSFORM Sum(d.dataval) as DataValue
SELECT d.fundid
FROM 
((Data d
INNER JOIN fund f ON d.fundid = f.fundid)
INNER JOIN  code c ON d.codeid = c.codeid)
INNER JOIN type t on c.typeid = t.typeid
GROUP BY d.fundid
PIVOT T.Typename

Results:

fundid  Balance Deposits    Withdraws   Yield
1       1000                            1.5
2       3000    40      60      1
3       2000            60      0.5

回复收藏 0 原文

~没有更多了~