需要查询帮助 [英] need help with query
问题描述
我有一个梦幻足球联赛，我在Access数据库中保存统计数据。我需要帮助提出一种方法来计算每支球队在整个职业生涯中的胜率。我所拥有的是一张包含以下字段的表格：
幻想团队
季节
周
W / L
自己的分数
Vs
对手
对手得分
游戏类型
W / L字段将具有W或L取决于它们是赢还是输，Vs字段将具有vs或@取决于其是否为主场或客场比赛，并且游戏类型字段告诉它是否是常规赛季，季后赛，超级碗比赛或马桶比赛。
我到目前为止所做的是我创建了一个查询上面的表格只列出了获胜者，这个新表名为Winners。然后我创建了一个名为Winners_Crosstab的交叉表查询，该查询计算每个团队每个赛季的胜利次数。所以现在我有总胜利。然后，我为损失做了同样的事情，最终得到了一个名为Loosers_Crosstab的查询。我做的最后一件事是创建另一个名为WinPercentage的查询，并添加了Winners_Crosstab和Loosers_Crosstab，并将Fantasy团队的两个表链接起来。这些领域包括：
幻想团队
胜利数量
损失数
胜利％
胜利％字段是我手动添加的字段，是一个计算
胜/ /（胜+亏）* 100
这个工作正常除非球队没有胜利或没有损失。如果一支球队以14-0领先，那么球队就不会出现在WinPercentage查询中。我理解为什么它不是，但我不知道怎么解决这个问题。我希望我能够明确地说明你给我一些指导。我是数据库新手，刚开始学习Access大约4个月前。
感谢您的帮助，
Scott
I have a fantasy football league that I am keeping stats for in an Access Database. I need help coming up with a way to calclulate the winning % for each team for their entire career. What I have is a table with the following fields:
Fantasy Team
Season
Week
W/L
Own Score
Vs
Opponent
Opponent Score
Game type
The W/L field will either have a W or a L depending if they won or loss, the Vs field will either have a vs or a @ depending if its a home or away game, and the Game Type field tells if it was a regular season game, playoff game, Super bowl game, or Toilet bowl game.
What I have done so far is I created a query on the above table to sort out just the winners and this new table is called Winners. I then created a crosstab query called Winners_Crosstab that counts the number of Wins each team has had for each season. So now I have totals wins. I then did the same thing for losses to end up with a query called Loosers_Crosstab. The last thing I did was to create another query called WinPercentage and added the Winners_Crosstab and Loosers_Crosstab and linked the two tables by Fantasy team. The fields include:
Fantasy Team
Count of Wins
Count of Losses
Win%
The win% field is one I added manually and is a calculation of
wins/(wins+losses)*100
This works fine except for when a team has no wins or no losses. If a team went 14-0 then the team doesn''t show up in the WinPercentage query. I understand why it doesn''t but I don''t know how to get around this. I hope I made this clear enough for you to give me some quidance. I am new to databases and just started learning Access about 4 months ago.
Thanks for your help,
Scott
推荐答案
对不起，忘了提到我正在使用Access 2003和Windows XP专业版。
Sorry, forgot to mention that I''m using Access 2003 and Windows XP professional.
你需要更改交叉表以便所有团队即使有0个赢家或0个输家也会出现。如果你想发布交叉表查询的SQL，我会看看它们。
You need to change the crosstabs so all teams appear even if there are 0 winners or 0 losers. If you want to post the SQL of the crosstab queries I''ll have a look at them.
这是我设置的交叉表查询，用于计算每个查询的超级碗数量。团队。
TRANSFORM计数（[SB Wins]。[W / L]）AS [CountOfW / L]
SELECT [SB Wins]。[Fantasy Team]，Count （[SB Wins]。[W / L]）AS [总和W]
来自[SB胜利]
GROUP BY [SB Wins]。[Fantasy Team]
PIVOT [SB Wins]。季节;
这是我设置的交叉表查询，用于计算每个团队的超级碗损失数量。 />
TRANSFORM计数（[SB损失]。[W / L]）AS [CountOfW / L]
SELECT [SB损失]。[幻想团队]，计数（[SB损失] ] [W / L]）AS [总计L]
来自[SB损失]
GROUP BY [SB损失]。[幻想团队]
PIVOT [SB损失]。季节;
SB Wins查询是在W / L字段标准设置为W的情况下设置的。并且SB损失查询是W / L字段标准设置为L的设置。
感谢您抽出宝贵时间帮助我。
This is the Crosstab Query I have set up to count the number of superbowl wins for each team.
TRANSFORM Count([SB Wins].[W/L]) AS [CountOfW/L]
SELECT [SB Wins].[Fantasy Team], Count([SB Wins].[W/L]) AS [Total Of W]
FROM [SB Wins]
GROUP BY [SB Wins].[Fantasy Team]
PIVOT [SB Wins].Season;
This is the Crosstab Query I have set up to count the number of superbowl losses for each team.
TRANSFORM Count([SB Losses].[W/L]) AS [CountOfW/L]
SELECT [SB Losses].[Fantasy Team], Count([SB Losses].[W/L]) AS [Total Of L]
FROM [SB Losses]
GROUP BY [SB Losses].[Fantasy Team]
PIVOT [SB Losses].Season;
SB Wins query is setup with the W/L field criteria set to "W" and the SB Losses query is the setup with the W/L field criteria set to "L".
Thank you for taking the time to help me.
这篇关于需要查询帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
