cannot get null value to equal 0

  • Thread starter Thread starter pat67
  • Start date Start date
P

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?
 
pat67 said:
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
Click to expand...

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;
 
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 said:
I am running a pool league where points are awarded for wins and
losses. I have a table that looks like this:
Click to expand...
Date   Round   Winner   Team   Points  Loser   Team1   Points1
Click to expand...
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
Click to expand...
I have the following query to total the points for winners:
Click to expand...
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;
Click to expand...
What I need to see is this:
Click to expand...
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
Click to expand...
Problem is what I am seeing is this:
Click to expand...
Date     Round    Team    WP
8/9/09      2           A        10
8/9/09      2           B        20
Click to expand...
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
Click to expand...

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 -
Click to expand...



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];
 
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.



pat67 said:
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:
Click to expand...
Date   Round   Winner   Team   Points  Loser   Team1   Points1
Click to expand...
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
Click to expand...
I have the following query to total the points for winners:
Click to expand...
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;
Click to expand...
What I need to see is this:
Click to expand...
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
Click to expand...
Problem is what I am seeing is this:
Click to expand...
Date     Round    Team    WP
8/9/09      2           A        10
8/9/09      2           B        20
Click to expand...
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
Click to expand...
Can anyone help?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

not sure i follow the second one. what does pat67 fill in for?
 
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.



pat67 said:
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:
Click to expand...
Date   Round   Winner   Team   Points  Loser   Team1   Points1
Click to expand...
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
Click to expand...
I have the following query to total the points for winners:
Click to expand...
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;
Click to expand...
What I need to see is this:
Click to expand...
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
Click to expand...
Problem is what I am seeing is this:
Click to expand...
Date     Round    Team    WP
8/9/09      2           A        10
8/9/09      2           B        20
Click to expand...
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
Click to expand...
Can anyone help?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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.
 
pat67 said:
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;
Click to expand...


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];
Click to expand...


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.
 
Back
Top