cannot get null value to equal 0

[pat67]

Here is my dilemma:
I am running a pool league where points are awarded for wins and
losses. I have a table that looks like this:

Date Round Winner Team Points Loser Team1 Points1

8/9/09 1 Joe A 10 Mike
B 4
8/9/09 1 Bob A 10 Steve B
2
8/9/09 1 Jim A 10 Bill
B 5
8/9/09 2 Joe A 10 Steve
B 4
8/9/09 2 Bill B 10 Bob
A 6
8/9/09 2 Mike B 10 Jim
A 4


I have the following query to total the points for winners:

SELECT tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team, Sum(tblResults_Points.Points) AS WP
FROM tblResults_Points
GROUP BY tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team;




What I need to see is this:

Date Round Team WP
8/9/09 1 A 30
8/9/09 1 B 0
8/9/09 2 A 10
8/9/09 2 B 20

Problem is what I am seeing is this:

Date Round Team WP
8/9/09 2 A 10
8/9/09 2 B 20

because there is no B in the Team field for round 1, I am getting no
values. It is the same for the query I run for loser points with no A
in the Team1 field for round 1

Can anyone help?

 

[Marshall Barton]

I am running a pool league where points are awarded for wins and
losses. I have a table that looks like this:

Date Round Winner Team Points Loser Team1 Points1

8/9/09 1 Joe A 10 Mike
B 4
8/9/09 1 Bob A 10 Steve B
2
8/9/09 1 Jim A 10 Bill
B 5
8/9/09 2 Joe A 10 Steve
B 4
8/9/09 2 Bill B 10 Bob
A 6
8/9/09 2 Mike B 10 Jim
A 4


I have the following query to total the points for winners:

SELECT tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team, Sum(tblResults_Points.Points) AS WP
FROM tblResults_Points
GROUP BY tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team;

What I need to see is this:

Date Round Team WP
8/9/09 1 A 30
8/9/09 1 B 0
8/9/09 2 A 10
8/9/09 2 B 20

Problem is what I am seeing is this:

Date Round Team WP
8/9/09 2 A 10
8/9/09 2 B 20

because there is no B in the Team field for round 1, I am getting no
values. It is the same for the query I run for loser points with no A
in the Team1 field for round 1

To include teams that are not in the points table, you need
to use an outer join to a table of teams:

SELECT tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team, Sum(tblResults_Points.Points) AS WP
FROM tblTeams Left Join tblResults_Points
ON tblTeams.teamID = tblResults_Points.Team
GROUP BY tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team;

 

[KARL DEWEY]

Try these two queries --
pat67 --
SELECT tblResults_Points.YourDate, tblResults_Points_1.Round,
tblResults_Points_2.Team
FROM tblResults_Points, tblResults_Points AS tblResults_Points_1,
tblResults_Points AS tblResults_Points_2
GROUP BY tblResults_Points.YourDate, tblResults_Points_1.Round,
tblResults_Points_2.Team;

SELECT pat67.YourDate, pat67.Round, pat67.Team, IIf(Sum([Points]) Is
Null,0,Sum([Points])) AS WP
FROM pat67 LEFT JOIN tblResults_Points ON (pat67.Team =
tblResults_Points.Team) AND (pat67.Round = tblResults_Points.Round) AND
(pat67.YourDate = tblResults_Points.YourDate)
GROUP BY pat67.YourDate, pat67.Round, pat67.Team;

 

[pat67]

I am running a pool league where points are awarded for wins and
losses. I have a table that looks like this:

Date   Round   Winner   Team   Points  Loser   Team1   Points1

8/9/09     1          Joe          A        10     Mike
B         4
8/9/09     1          Bob         A          10    Steve     B
2
8/9/09     1          Jim          A         10    Bill
B         5
8/9/09     2          Joe          A         10    Steve
B         4
8/9/09     2          Bill          B           10   Bob
A         6
8/9/09     2          Mike       B           10    Jim
A         4

I have the following query to total the points for winners:

SELECT tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team, Sum(tblResults_Points.Points) AS WP
FROM tblResults_Points
GROUP BY tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team;

What I need to see is this:

Date     Round    Team    WP
8/9/09      1           A        30
8/9/09      1           B         0
8/9/09      2           A        10
8/9/09      2           B        20

Problem is what I am seeing is this:

Date     Round    Team    WP
8/9/09      2           A        10
8/9/09      2           B        20

because there is no B in the Team field for round 1, I am getting no
values. It is the same for the query I run for loser points with no A
in the Team1 field for round 1

To include teams that are not in the points table, you need
to use an outer join to a table of teams:

SELECT tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team, Sum(tblResults_Points.Points) AS WP
FROM tblTeams Left Join tblResults_Points
                ON tblTeams.teamID = tblResults_Points.Team
GROUP BY tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team;

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Thanks for the info. I tried your query, but it still does not work. I
will give you the actual query since my tables are different. In any
event, What I got was a team to show up that had no games played at
all

SELECT tblResults_Points.Date, tblResults_Points.Round, [Teams in
League].[Team Name], Sum(tblResults_Points.Points) AS WP
FROM [Teams in League] LEFT JOIN tblResults_Points ON [Teams in
League].[Team Name] = tblResults_Points.Team
GROUP BY tblResults_Points.Date, tblResults_Points.Round, [Teams in
League].[Team Name];

 

[pat67]

Try these two queries --
     pat67 --
SELECT tblResults_Points.YourDate, tblResults_Points_1.Round,
tblResults_Points_2.Team
FROM tblResults_Points, tblResults_Points AS tblResults_Points_1,
tblResults_Points AS tblResults_Points_2
GROUP BY tblResults_Points.YourDate, tblResults_Points_1.Round,
tblResults_Points_2.Team;

SELECT pat67.YourDate, pat67.Round, pat67.Team, IIf(Sum([Points]) Is
Null,0,Sum([Points])) AS WP
FROM pat67 LEFT JOIN tblResults_Points ON (pat67.Team =
tblResults_Points.Team) AND (pat67.Round = tblResults_Points.Round) AND
(pat67.YourDate = tblResults_Points.YourDate)
GROUP BY pat67.YourDate, pat67.Round, pat67.Team;

--
Build a little, test a little.

Here is my dilemma:
I am running a pool league where points are awarded for wins and
losses. I have a table that looks like this:

Date   Round   Winner   Team   Points  Loser   Team1   Points1

8/9/09     1          Joe          A         10     Mike
B         4
8/9/09     1          Bob         A          10    Steve     B
2
8/9/09     1          Jim          A          10    Bill
B         5
8/9/09     2          Joe          A          10    Steve
B         4
8/9/09     2          Bill          B           10   Bob
A         6
8/9/09     2          Mike       B           10    Jim
A         4

I have the following query to total the points for winners:

SELECT tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team, Sum(tblResults_Points.Points) AS WP
FROM tblResults_Points
GROUP BY tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team;

What I need to see is this:

Date     Round    Team    WP
8/9/09      1           A        30
8/9/09      1           B         0
8/9/09      2           A        10
8/9/09      2           B        20

Problem is what I am seeing is this:

Date     Round    Team    WP
8/9/09      2           A        10
8/9/09      2           B        20

because there is no B in the Team field for round 1, I am getting no
values. It is the same for the query I run for loser points with no A
in the Team1 field for round 1

Can anyone help?- Hide quoted text -

- Show quoted text -

not sure i follow the second one. what does pat67 fill in for?

 

[pat67]

Try these two queries --
     pat67 --
SELECT tblResults_Points.YourDate, tblResults_Points_1.Round,
tblResults_Points_2.Team
FROM tblResults_Points, tblResults_Points AS tblResults_Points_1,
tblResults_Points AS tblResults_Points_2
GROUP BY tblResults_Points.YourDate, tblResults_Points_1.Round,
tblResults_Points_2.Team;

SELECT pat67.YourDate, pat67.Round, pat67.Team, IIf(Sum([Points]) Is
Null,0,Sum([Points])) AS WP
FROM pat67 LEFT JOIN tblResults_Points ON (pat67.Team =
tblResults_Points.Team) AND (pat67.Round = tblResults_Points.Round) AND
(pat67.YourDate = tblResults_Points.YourDate)
GROUP BY pat67.YourDate, pat67.Round, pat67.Team;

--
Build a little, test a little.

Here is my dilemma:
I am running a pool league where points are awarded for wins and
losses. I have a table that looks like this:

Date   Round   Winner   Team   Points  Loser   Team1   Points1

8/9/09     1          Joe          A         10     Mike
B         4
8/9/09     1          Bob         A          10    Steve     B
2
8/9/09     1          Jim          A          10    Bill
B         5
8/9/09     2          Joe          A          10    Steve
B         4
8/9/09     2          Bill          B           10   Bob
A         6
8/9/09     2          Mike       B           10    Jim
A         4

I have the following query to total the points for winners:

SELECT tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team, Sum(tblResults_Points.Points) AS WP
FROM tblResults_Points
GROUP BY tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team;

What I need to see is this:

Date     Round    Team    WP
8/9/09      1           A        30
8/9/09      1           B         0
8/9/09      2           A        10
8/9/09      2           B        20

Problem is what I am seeing is this:

Date     Round    Team    WP
8/9/09      2           A        10
8/9/09      2           B        20

because there is no B in the Team field for round 1, I am getting no
values. It is the same for the query I run for loser points with no A
in the Team1 field for round 1

Can anyone help?- Hide quoted text -

- Show quoted text -

Ok. i am an idiot. i just realized that the second query is based on
the first. And i did it and it seems to be fine. Thanks a lot. I will
let you know if i run into any problems.

 

[Marshall Barton]

To include teams that are not in the points table, you need
to use an outer join to a table of teams:

SELECT tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team, Sum(tblResults_Points.Points) AS WP
FROM tblTeams Left Join tblResults_Points
                ON tblTeams.teamID = tblResults_Points.Team
GROUP BY tblResults_Points.Date, tblResults_Points.Round,
tblResults_Points.Team;

Thanks for the info. I tried your query, but it still does not work. I
will give you the actual query since my tables are different. In any
event, What I got was a team to show up that had no games played at
all

SELECT tblResults_Points.Date, tblResults_Points.Round, [Teams in
League].[Team Name], Sum(tblResults_Points.Points) AS WP
FROM [Teams in League] LEFT JOIN tblResults_Points ON [Teams in
League].[Team Name] = tblResults_Points.Team
GROUP BY tblResults_Points.Date, tblResults_Points.Round, [Teams in
League].[Team Name];

I would expect that since you want a score for teams that
did not play.

As an aside, what the bleep did you use to compose your
message? My newsreader program displayed a bunch of =A0 and
3D interspersed throughout your post.