Adding a value to an unmatched record

[David McKnight]

My current query is:

SELECT [Scores-Win Loss].Season, [Scores-Win Loss].Visitor, [Scores-Win
Loss].Home, [122 Power Rankings DB].[Power Ranking], [122 Power Rankings
DB_1].[Power Ranking]
FROM [122 Power Rankings DB] AS [122 Power Rankings DB_1] INNER JOIN ([122
Power Rankings DB] INNER JOIN [Scores-Win Loss] ON ([122 Power Rankings
DB].Season = [Scores-Win Loss].Season) AND ([122 Power Rankings DB].Home =
[Scores-Win Loss].Visitor)) ON ([122 Power Rankings DB_1].Season =
[Scores-Win Loss].Season) AND ([122 Power Rankings DB_1].Home = [Scores-Win
Loss].Home);

Is there a way to add a value to the [122 Power Rankings DB] & [122 Power
Rankings DB_1] when ever there is not a match from the [Scores-Win Loss]
query?

I would like the value to equal "0" when ever there is not a record from the
[122 Power Ranking DB] in the new query.

fyi : [122 Power Rankings DB] & [122 Power Rankings DB_1] are infact the
same data (don't ask why I set it up this way, too many other things to
change at this point).

 

[Tom van Stiphout]

On Thu, 22 Jan 2009 18:16:06 -0800, David McKnight

I don't understand your question completely, but perhaps the Nz
function will help. It can turn null values into 0.

-Tom.
Microsoft Access MVP

 

[Marshall Barton]

My current query is:

SELECT [Scores-Win Loss].Season, [Scores-Win Loss].Visitor, [Scores-Win
Loss].Home, [122 Power Rankings DB].[Power Ranking], [122 Power Rankings
DB_1].[Power Ranking]
FROM [122 Power Rankings DB] AS [122 Power Rankings DB_1] INNER JOIN ([122
Power Rankings DB] INNER JOIN [Scores-Win Loss] ON ([122 Power Rankings
DB].Season = [Scores-Win Loss].Season) AND ([122 Power Rankings DB].Home =
[Scores-Win Loss].Visitor)) ON ([122 Power Rankings DB_1].Season =
[Scores-Win Loss].Season) AND ([122 Power Rankings DB_1].Home = [Scores-Win
Loss].Home);

Is there a way to add a value to the [122 Power Rankings DB] & [122 Power
Rankings DB_1] when ever there is not a match from the [Scores-Win Loss]
query?

I would like the value to equal "0" when ever there is not a record from the
[122 Power Ranking DB] in the new query.

fyi : [122 Power Rankings DB] & [122 Power Rankings DB_1] are infact the
same data (don't ask why I set it up this way, too many other things to
change at this point).

The query needs to use an outer join from the scores table,
then the field values in the unmatched records will be Null.
Use the Nz function to convert Nulls to zero.

This may not be exactly what you want, but should give you
the idea:

SELECT SVisitor,
S.Home,
Nz(PR.[Power Ranking], 0),
Nz(PR1.[Power Ranking], 0),
FROM ([Scores-Win Loss] As S
LEFT JOIN [122 Power Rankings DB] AS PR
ON PR.Season = S.Season
AND PR.Home = S.Visitor)
LEFT JOIN [122 Power Rankings DB_1] As PR1
ON PR1.Season = S.Season
AND PR1.Home = S.Home

 

[David McKnight]

Thanks I think this is on the right track of what I'm try to do. I get
"Syntax on JOIN operation" When I use the following code:

SELECT [Scores-Win Loss].Season, [Scores-Win Loss].Visitor, [Scores-Win
Loss].Home, Nz([122 Power Rankings DB].[Power Ranking],0), Nz([122 Power
Rankings DB_1].[Power Ranking],0) FROM [Scores-Win Loss] AS [Scores-Win Loss]
LEFT JOIN ([122 Power Rankings DB] AS [122 Power Rankings DB] ON [122 Power
Rankings DB].Season =[ Scores-Win Loss].Season AND [122 Power Rankings
DB].Home = [ Scores-Win Loss].Visitor LEFT JOIN ([122 Power Rankings DB_1] AS
[122 Power Rankings DB_1] ON [122 Power Rankings DB_1].Season =[ Scores-Win
Loss].Season AND [122 Power Rankings DB_1].Home = [ Scores-Win Loss].Home,

--
David McKnight

My current query is:

SELECT [Scores-Win Loss].Season, [Scores-Win Loss].Visitor, [Scores-Win
Loss].Home, [122 Power Rankings DB].[Power Ranking], [122 Power Rankings
DB_1].[Power Ranking]
FROM [122 Power Rankings DB] AS [122 Power Rankings DB_1] INNER JOIN ([122
Power Rankings DB] INNER JOIN [Scores-Win Loss] ON ([122 Power Rankings
DB].Season = [Scores-Win Loss].Season) AND ([122 Power Rankings DB].Home =
[Scores-Win Loss].Visitor)) ON ([122 Power Rankings DB_1].Season =
[Scores-Win Loss].Season) AND ([122 Power Rankings DB_1].Home = [Scores-Win
Loss].Home);

Is there a way to add a value to the [122 Power Rankings DB] & [122 Power
Rankings DB_1] when ever there is not a match from the [Scores-Win Loss]
query?

I would like the value to equal "0" when ever there is not a record from the
[122 Power Ranking DB] in the new query.

fyi : [122 Power Rankings DB] & [122 Power Rankings DB_1] are infact the
same data (don't ask why I set it up this way, too many other things to
change at this point).

The query needs to use an outer join from the scores table,
then the field values in the unmatched records will be Null.
Use the Nz function to convert Nulls to zero.

This may not be exactly what you want, but should give you
the idea:

SELECT SVisitor,
S.Home,
Nz(PR.[Power Ranking], 0),
Nz(PR1.[Power Ranking], 0),
FROM ([Scores-Win Loss] As S
LEFT JOIN [122 Power Rankings DB] AS PR
ON PR.Season = S.Season
AND PR.Home = S.Visitor)
LEFT JOIN [122 Power Rankings DB_1] As PR1
ON PR1.Season = S.Season
AND PR1.Home = S.Home

 

[Marshall Barton]

Thanks I think this is on the right track of what I'm try to do. I get
"Syntax on JOIN operation" When I use the following code:

SELECT [Scores-Win Loss].Season, [Scores-Win Loss].Visitor, [Scores-Win
Loss].Home, Nz([122 Power Rankings DB].[Power Ranking],0), Nz([122 Power
Rankings DB_1].[Power Ranking],0) FROM [Scores-Win Loss] AS [Scores-Win Loss]
LEFT JOIN ([122 Power Rankings DB] AS [122 Power Rankings DB] ON [122 Power
Rankings DB].Season =[ Scores-Win Loss].Season AND [122 Power Rankings
DB].Home = [ Scores-Win Loss].Visitor LEFT JOIN ([122 Power Rankings DB_1] AS
[122 Power Rankings DB_1] ON [122 Power Rankings DB_1].Season =[ Scores-Win
Loss].Season AND [122 Power Rankings DB_1].Home = [ Scores-Win Loss].Home,

Man is that difficult to read. Why don't you alias those
long table names to short and simple names>

Did you retype that into your post? It doesn't look like a
legal statement with extra spaces and unbalanced
parenthesis. Always use Copy/Paste when posting your code,
expressions or SQL.

It also looks like the Parenthesis I used around the joins
are different, but with some of them missing, I can't be
sure.