Inner , Right, Left JOIN

  • Thread starter Thread starter David McKnight
  • Start date Start date
D

David McKnight

I have a query :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division] INNER JOIN ([School Season Division] AS
[School Season Division_1] INNER JOIN Scores ON ([School Season
Division_1].Home = Scores.Home) AND ([School Season Division_1].Season =
Scores.Season)) ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

I would like to make it to make it a left or right join (not sure which) on
season & home so that when there is missing data from the [School Season
Division] table I get a blank in the query for that field/recordset. I've
tried left and right joins but I get errors that state it is ambiguious.
 
Try this:

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));
 
Yes this worked for query, but it doesn't work when it is in a form-code
(sorry I didn't mention that) such as :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
"WHERE (((Scores.Season)= " & Me.txtSeason.Value & "));"
Click to expand...
Erroor says "join function is not supported" - is there a work around?
--
David McKnight


Ken Snell said:
Try this:

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



David McKnight said:
I have a query :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division] INNER JOIN ([School Season Division] AS
[School Season Division_1] INNER JOIN Scores ON ([School Season
Division_1].Home = Scores.Home) AND ([School Season Division_1].Season =
Scores.Season)) ON (Scores.Home = [School Season Division].Home) AND
([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

I would like to make it to make it a left or right join (not sure which)
on
season & home so that when there is missing data from the [School Season
Division] table I get a blank in the query for that field/recordset. I've
tried left and right joins but I get errors that state it is ambiguious.
Click to expand...
Click to expand...
 
One more fact. Season Fields will always be populated (no blanks) on both
tables.
--
David McKnight


David McKnight said:
Yes this worked for query, but it doesn't work when it is in a form-code
(sorry I didn't mention that) such as :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
"WHERE (((Scores.Season)= " & Me.txtSeason.Value & "));"
Click to expand...
Erroor says "join function is not supported" - is there a work around?
--
David McKnight


Ken Snell said:
Try this:

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



David McKnight said:
I have a query :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division] INNER JOIN ([School Season Division] AS
[School Season Division_1] INNER JOIN Scores ON ([School Season
Division_1].Home = Scores.Home) AND ([School Season Division_1].Season =
Scores.Season)) ON (Scores.Home = [School Season Division].Home) AND
([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

I would like to make it to make it a left or right join (not sure which)
on
season & home so that when there is missing data from the [School Season
Division] table I get a blank in the query for that field/recordset. I've
tried left and right joins but I get errors that state it is ambiguious.
Click to expand...
Click to expand...
Click to expand...
 
Same issue as with Ken S. reply.
--
David McKnight


ErezM via AccessMonster.com said:
hi

SELECT scores.season, scores.visitor, scores.home, scores.margin
FROM (scores LEFT JOIN [school season division] ON (scores.season = [school
season division].season) AND (scores.home = [school season division].home))
LEFT JOIN [school season division] AS [school season division_1] ON (scores.
home = [school season division_1].home) AND (scores.season = [school season
division_1].season);

and include the where at the end

Erez

David said:
I have a query :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division] INNER JOIN ([School Season Division] AS
[School Season Division_1] INNER JOIN Scores ON ([School Season
Division_1].Home = Scores.Home) AND ([School Season Division_1].Season =
Scores.Season)) ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

I would like to make it to make it a left or right join (not sure which) on
season & home so that when there is missing data from the [School Season
Division] table I get a blank in the query for that field/recordset. I've
tried left and right joins but I get errors that state it is ambiguious.
Click to expand...
Click to expand...
 
"Form-code"? Are you trying to build the SQL statement in VBA code? Or
something else?

Post the code that you're using if that is what you're doing. Also, what
types of values (show examples) will be in the txtSeason textbox.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



David McKnight said:
Yes this worked for query, but it doesn't work when it is in a form-code
(sorry I didn't mention that) such as :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
"WHERE (((Scores.Season)= " & Me.txtSeason.Value & "));"
Click to expand...
Erroor says "join function is not supported" - is there a work around?
--
David McKnight


Ken Snell said:
Try this:

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
I have a query :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division] INNER JOIN ([School Season Division] AS
[School Season Division_1] INNER JOIN Scores ON ([School Season
Division_1].Home = Scores.Home) AND ([School Season Division_1].Season
=
Scores.Season)) ON (Scores.Home = [School Season Division].Home) AND
([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

I would like to make it to make it a left or right join (not sure
which)
on
season & home so that when there is missing data from the [School
Season
Division] table I get a blank in the query for that field/recordset.
I've
tried left and right joins but I get errors that state it is
ambiguious.
Click to expand...
Click to expand...
Click to expand...
 
Yes, I'm bulding SQL statement with VBA code. Essentially the statement below
is the code, but I've truncated out non relavant, but here it is just in
case. The txtSeason text box has values such as "1974 or 2007".


qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season, [Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score], Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS [Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin])))) AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin) AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division - Home],
+[School Season Division_1.Division] AS [School Season Division - Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home]) &
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home]) &
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) & RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
"FROM Coefficients, [School Season Division] INNER JOIN ([School Season
Division] AS [School Season Division_1] INNER JOIN Scores ON " & _
"([School Season Division_1].Home = Scores.Visitor)AND ([School Season
Division_1].Season = Scores.Season)) ON ([School Season Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And (Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight


Ken Snell said:
"Form-code"? Are you trying to build the SQL statement in VBA code? Or
something else?

Post the code that you're using if that is what you're doing. Also, what
types of values (show examples) will be in the txtSeason textbox.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



David McKnight said:
Yes this worked for query, but it doesn't work when it is in a form-code
(sorry I didn't mention that) such as :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
"WHERE (((Scores.Season)= " & Me.txtSeason.Value & "));"
Click to expand...
Erroor says "join function is not supported" - is there a work around?
--
David McKnight


Ken Snell said:
Try this:

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
I have a query :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division] INNER JOIN ([School Season Division] AS
[School Season Division_1] INNER JOIN Scores ON ([School Season
Division_1].Home = Scores.Home) AND ([School Season Division_1].Season
=
Scores.Season)) ON (Scores.Home = [School Season Division].Home) AND
([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

I would like to make it to make it a left or right join (not sure
which)
on
season & home so that when there is missing data from the [School
Season
Division] table I get a blank in the query for that field/recordset.
I've
tried left and right joins but I get errors that state it is
ambiguious.
Click to expand...
Click to expand...
Click to expand...
 
You appear to be missing a required space before the WHERE word, which may
be the cause of your error:

Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And (Scores.Season)<=
" & Me.txtEndSeason.Value & "));"

The above should be

Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And (Scores.Season)<=
" & Me.txtEndSeason.Value & "));"


You said ' The txtSeason text box has values such as "1974 or 2007" '. Do
you mean the text box would contain the value 1874, or the value 2007? I
trust that you don't mean "1974 or 2007" as a literal string in the textbox,
as that will not work in your query for the criterion.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



David McKnight said:
Yes, I'm bulding SQL statement with VBA code. Essentially the statement
below
is the code, but I've truncated out non relavant, but here it is just in
case. The txtSeason text box has values such as "1974 or 2007".


qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season,
[Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score], Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS
[Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin])))) AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin) AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division - Home],
+[School Season Division_1.Division] AS [School Season Division -
Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
"FROM Coefficients, [School Season Division] INNER JOIN ([School Season
Division] AS [School Season Division_1] INNER JOIN Scores ON " & _
"([School Season Division_1].Home = Scores.Visitor)AND ([School Season
Division_1].Season = Scores.Season)) ON ([School Season Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight


Ken Snell said:
"Form-code"? Are you trying to build the SQL statement in VBA code? Or
something else?

Post the code that you're using if that is what you're doing. Also, what
types of values (show examples) will be in the txtSeason textbox.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Yes this worked for query, but it doesn't work when it is in a
form-code
(sorry I didn't mention that) such as :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
"WHERE (((Scores.Season)= " & Me.txtSeason.Value & "));"

Erroor says "join function is not supported" - is there a work around?
--
David McKnight


:

Try this:

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
I have a query :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division] INNER JOIN ([School Season Division]
AS
[School Season Division_1] INNER JOIN Scores ON ([School Season
Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season
=
Scores.Season)) ON (Scores.Home = [School Season Division].Home) AND
([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

I would like to make it to make it a left or right join (not sure
which)
on
season & home so that when there is missing data from the [School
Season
Division] table I get a blank in the query for that field/recordset.
I've
tried left and right joins but I get errors that state it is
ambiguious.
Click to expand...
Click to expand...
Click to expand...
 
Yes, I'm bulding SQL statement with VBA code. Essentially the statement below
is the code, but I've truncated out non relavant, but here it is just in
case. The txtSeason text box has values such as "1974 or 2007".
Click to expand...

You cannot pass operators such as OR using a parameter - this will find
records where the value in Season is the literal text string

"1974 or 2007"

or it will fail altogether.
 
I added space before WHERE but still get same error "JOIN function not
supported". Works with INNER but not LEFT or RIGHT join.
Yes I did meam to say that txtSeason could have values such as 2007, or a
value such as 1974, etc.

qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season, [Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score], Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS [Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin])))) AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin) AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division - Home],
+[School Season Division_1.Division] AS [School Season Division - Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home]) &
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home]) &
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) & RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
" FROM Coefficients, [School Season Division] RIGHT JOIN ([School Season
Division] AS [School Season Division_1] RIGHT JOIN Scores ON " & _
"([School Season Division_1].Home = Scores.Visitor)AND ([School Season
Division_1].Season = Scores.Season)) ON ([School Season Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And (Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight


Ken Snell said:
You appear to be missing a required space before the WHERE word, which may
be the cause of your error:

Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And (Scores.Season)<=
" & Me.txtEndSeason.Value & "));"

The above should be

Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And (Scores.Season)<=
" & Me.txtEndSeason.Value & "));"


You said ' The txtSeason text box has values such as "1974 or 2007" '. Do
you mean the text box would contain the value 1874, or the value 2007? I
trust that you don't mean "1974 or 2007" as a literal string in the textbox,
as that will not work in your query for the criterion.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



David McKnight said:
Yes, I'm bulding SQL statement with VBA code. Essentially the statement
below
is the code, but I've truncated out non relavant, but here it is just in
case. The txtSeason text box has values such as "1974 or 2007".


qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season,
[Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score], Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS
[Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin])))) AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin) AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division - Home],
+[School Season Division_1.Division] AS [School Season Division -
Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
"FROM Coefficients, [School Season Division] INNER JOIN ([School Season
Division] AS [School Season Division_1] INNER JOIN Scores ON " & _
"([School Season Division_1].Home = Scores.Visitor)AND ([School Season
Division_1].Season = Scores.Season)) ON ([School Season Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight


Ken Snell said:
"Form-code"? Are you trying to build the SQL statement in VBA code? Or
something else?

Post the code that you're using if that is what you're doing. Also, what
types of values (show examples) will be in the txtSeason textbox.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Yes this worked for query, but it doesn't work when it is in a
form-code
(sorry I didn't mention that) such as :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
"WHERE (((Scores.Season)= " & Me.txtSeason.Value & "));"

Erroor says "join function is not supported" - is there a work around?
--
David McKnight


:

Try this:

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
I have a query :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division] INNER JOIN ([School Season Division]
AS
[School Season Division_1] INNER JOIN Scores ON ([School Season
Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season
=
Scores.Season)) ON (Scores.Home = [School Season Division].Home) AND
([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

I would like to make it to make it a left or right join (not sure
which)
on
season & home so that when there is missing data from the [School
Season
Division] table I get a blank in the query for that field/recordset.
I've
tried left and right joins but I get errors that state it is
ambiguious.
Click to expand...
Click to expand...
Click to expand...
 
Add a step to your code to print out the SQL statement that you build:
Debug.Print qdf.SQL

take a look at the string and make sure that it's the correct syntax. Post
it here if you don't see anything obvious.

Also, paste the printed-out SQL string into a new query (SQL View). Will it
run? Switch back to design view with it, then back to SQL view -- ACCESS
will "change" the statement to what it likes. Try using that syntax for
building your SQL statement in code.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


David McKnight said:
I added space before WHERE but still get same error "JOIN function not
supported". Works with INNER but not LEFT or RIGHT join.
Yes I did meam to say that txtSeason could have values such as 2007, or a
value such as 1974, etc.

qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season,
[Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score], Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS
[Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin])))) AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin) AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division - Home],
+[School Season Division_1.Division] AS [School Season Division -
Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
" FROM Coefficients, [School Season Division] RIGHT JOIN ([School Season
Division] AS [School Season Division_1] RIGHT JOIN Scores ON " & _
"([School Season Division_1].Home = Scores.Visitor)AND ([School Season
Division_1].Season = Scores.Season)) ON ([School Season Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight


Ken Snell said:
You appear to be missing a required space before the WHERE word, which
may
be the cause of your error:

Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"

The above should be

Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"


You said ' The txtSeason text box has values such as "1974 or 2007" '. Do
you mean the text box would contain the value 1874, or the value 2007? I
trust that you don't mean "1974 or 2007" as a literal string in the
textbox,
as that will not work in your query for the criterion.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Yes, I'm bulding SQL statement with VBA code. Essentially the statement
below
is the code, but I've truncated out non relavant, but here it is just
in
case. The txtSeason text box has values such as "1974 or 2007".


qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season,
[Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score],
Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS
[Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin]))))
AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin) AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division - Home],
+[School Season Division_1.Division] AS [School Season Division -
Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
"FROM Coefficients, [School Season Division] INNER JOIN ([School Season
Division] AS [School Season Division_1] INNER JOIN Scores ON " & _
"([School Season Division_1].Home = Scores.Visitor)AND ([School Season
Division_1].Season = Scores.Season)) ON ([School Season
Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight


:

"Form-code"? Are you trying to build the SQL statement in VBA code? Or
something else?

Post the code that you're using if that is what you're doing. Also,
what
types of values (show examples) will be in the txtSeason textbox.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Yes this worked for query, but it doesn't work when it is in a
form-code
(sorry I didn't mention that) such as :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School
Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
"WHERE (((Scores.Season)= " & Me.txtSeason.Value & "));"

Erroor says "join function is not supported" - is there a work
around?
--
David McKnight


:

Try this:

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School
Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
I have a query :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division] INNER JOIN ([School Season
Division]
AS
[School Season Division_1] INNER JOIN Scores ON ([School Season
Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season
=
Scores.Season)) ON (Scores.Home = [School Season Division].Home)
AND
([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

I would like to make it to make it a left or right join (not sure
which)
on
season & home so that when there is missing data from the [School
Season
Division] table I get a blank in the query for that
field/recordset.
I've
tried left and right joins but I get errors that state it is
ambiguious.
Click to expand...
Click to expand...
Click to expand...
 
Still no luck, no obvious syntax issues. Copied and pasted into SQL
view(removed " & _) Same result as before, with INNER JOIN works fine, RIGHT
JOIN " JOIN expression not supported. Running Access 2007 saved in Access
2007 format.
--
David McKnight


Ken Snell said:
Add a step to your code to print out the SQL statement that you build:
Debug.Print qdf.SQL

take a look at the string and make sure that it's the correct syntax. Post
it here if you don't see anything obvious.

Also, paste the printed-out SQL string into a new query (SQL View). Will it
run? Switch back to design view with it, then back to SQL view -- ACCESS
will "change" the statement to what it likes. Try using that syntax for
building your SQL statement in code.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


David McKnight said:
I added space before WHERE but still get same error "JOIN function not
supported". Works with INNER but not LEFT or RIGHT join.
Yes I did meam to say that txtSeason could have values such as 2007, or a
value such as 1974, etc.

qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season,
[Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score], Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS
[Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin])))) AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin) AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division - Home],
+[School Season Division_1.Division] AS [School Season Division -
Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
" FROM Coefficients, [School Season Division] RIGHT JOIN ([School Season
Division] AS [School Season Division_1] RIGHT JOIN Scores ON " & _
"([School Season Division_1].Home = Scores.Visitor)AND ([School Season
Division_1].Season = Scores.Season)) ON ([School Season Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight


Ken Snell said:
You appear to be missing a required space before the WHERE word, which
may
be the cause of your error:

Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"

The above should be

Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"


You said ' The txtSeason text box has values such as "1974 or 2007" '. Do
you mean the text box would contain the value 1874, or the value 2007? I
trust that you don't mean "1974 or 2007" as a literal string in the
textbox,
as that will not work in your query for the criterion.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Yes, I'm bulding SQL statement with VBA code. Essentially the statement
below
is the code, but I've truncated out non relavant, but here it is just
in
case. The txtSeason text box has values such as "1974 or 2007".


qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season,
[Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score],
Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS
[Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin]))))
AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin) AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division - Home],
+[School Season Division_1.Division] AS [School Season Division -
Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
"FROM Coefficients, [School Season Division] INNER JOIN ([School Season
Division] AS [School Season Division_1] INNER JOIN Scores ON " & _
"([School Season Division_1].Home = Scores.Visitor)AND ([School Season
Division_1].Season = Scores.Season)) ON ([School Season
Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight


:

"Form-code"? Are you trying to build the SQL statement in VBA code? Or
something else?

Post the code that you're using if that is what you're doing. Also,
what
types of values (show examples) will be in the txtSeason textbox.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Yes this worked for query, but it doesn't work when it is in a
form-code
(sorry I didn't mention that) such as :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School
Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
"WHERE (((Scores.Season)= " & Me.txtSeason.Value & "));"

Erroor says "join function is not supported" - is there a work
around?
--
David McKnight


:

Try this:

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School
Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
I have a query :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division] INNER JOIN ([School Season
Division]
AS
[School Season Division_1] INNER JOIN Scores ON ([School Season
Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season
=
Scores.Season)) ON (Scores.Home = [School Season Division].Home)
AND
([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

I would like to make it to make it a left or right join (not sure
which)
on
season & home so that when there is missing data from the [School
Season
Division] table I get a blank in the query for that
field/recordset.
I've
tried left and right joins but I get errors that state it is
ambiguious.
Click to expand...
Click to expand...
Click to expand...
 
I don't know what else to suggest, sorry.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


David McKnight said:
Still no luck, no obvious syntax issues. Copied and pasted into SQL
view(removed " & _) Same result as before, with INNER JOIN works fine,
RIGHT
JOIN " JOIN expression not supported. Running Access 2007 saved in Access
2007 format.
--
David McKnight


Ken Snell said:
Add a step to your code to print out the SQL statement that you build:
Debug.Print qdf.SQL

take a look at the string and make sure that it's the correct syntax.
Post
it here if you don't see anything obvious.

Also, paste the printed-out SQL string into a new query (SQL View). Will
it
run? Switch back to design view with it, then back to SQL view -- ACCESS
will "change" the statement to what it likes. Try using that syntax for
building your SQL statement in code.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
I added space before WHERE but still get same error "JOIN function not
supported". Works with INNER but not LEFT or RIGHT join.
Yes I did meam to say that txtSeason could have values such as 2007, or
a
value such as 1974, etc.

qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season,
[Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score],
Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS
[Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin]))))
AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin) AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division - Home],
+[School Season Division_1.Division] AS [School Season Division -
Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
" FROM Coefficients, [School Season Division] RIGHT JOIN ([School
Season
Division] AS [School Season Division_1] RIGHT JOIN Scores ON " & _
"([School Season Division_1].Home = Scores.Visitor)AND ([School Season
Division_1].Season = Scores.Season)) ON ([School Season
Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight


:

You appear to be missing a required space before the WHERE word, which
may
be the cause of your error:

Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"

The above should be

Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"


You said ' The txtSeason text box has values such as "1974 or 2007" '.
Do
you mean the text box would contain the value 1874, or the value 2007?
I
trust that you don't mean "1974 or 2007" as a literal string in the
textbox,
as that will not work in your query for the criterion.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Yes, I'm bulding SQL statement with VBA code. Essentially the
statement
below
is the code, but I've truncated out non relavant, but here it is
just
in
case. The txtSeason text box has values such as "1974 or 2007".


qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season,
[Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score],
Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS
[Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin]))))
AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin)
AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division -
Home],
+[School Season Division_1.Division] AS [School Season Division -
Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
"FROM Coefficients, [School Season Division] INNER JOIN ([School
Season
Division] AS [School Season Division_1] INNER JOIN Scores ON " & _
"([School Season Division_1].Home = Scores.Visitor)AND ([School
Season
Division_1].Season = Scores.Season)) ON ([School Season
Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)" &
_
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight


:

"Form-code"? Are you trying to build the SQL statement in VBA code?
Or
something else?

Post the code that you're using if that is what you're doing. Also,
what
types of values (show examples) will be in the txtSeason textbox.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Yes this worked for query, but it doesn't work when it is in a
form-code
(sorry I didn't mention that) such as :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School
Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
"WHERE (((Scores.Season)= " & Me.txtSeason.Value & "));"

Erroor says "join function is not supported" - is there a work
around?
--
David McKnight


:

Try this:

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School
Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



in
message
I have a query :

SELECT Scores.Season, Scores.Visitor, Scores.Home,
Scores.Margin
FROM [School Season Division] INNER JOIN ([School Season
Division]
AS
[School Season Division_1] INNER JOIN Scores ON ([School
Season
Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season
=
Scores.Season)) ON (Scores.Home = [School Season
Division].Home)
AND
([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

I would like to make it to make it a left or right join (not
sure
which)
on
season & home so that when there is missing data from the
[School
Season
Division] table I get a blank in the query for that
field/recordset.
I've
tried left and right joins but I get errors that state it is
ambiguious.
Click to expand...
Click to expand...
Click to expand...
 
I found a work around.

Created a additional field in each table called [SeasonHome] which was just
=[Season] &[Home] I was then able to right join these without any problem.
--
David McKnight


Ken Snell said:
I don't know what else to suggest, sorry.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


David McKnight said:
Still no luck, no obvious syntax issues. Copied and pasted into SQL
view(removed " & _) Same result as before, with INNER JOIN works fine,
RIGHT
JOIN " JOIN expression not supported. Running Access 2007 saved in Access
2007 format.
--
David McKnight


Ken Snell said:
Add a step to your code to print out the SQL statement that you build:
Debug.Print qdf.SQL

take a look at the string and make sure that it's the correct syntax.
Post
it here if you don't see anything obvious.

Also, paste the printed-out SQL string into a new query (SQL View). Will
it
run? Switch back to design view with it, then back to SQL view -- ACCESS
will "change" the statement to what it likes. Try using that syntax for
building your SQL statement in code.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
I added space before WHERE but still get same error "JOIN function not
supported". Works with INNER but not LEFT or RIGHT join.
Yes I did meam to say that txtSeason could have values such as 2007, or
a
value such as 1974, etc.

qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season,
[Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score],
Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS
[Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin]))))
AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin) AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division - Home],
+[School Season Division_1.Division] AS [School Season Division -
Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
" FROM Coefficients, [School Season Division] RIGHT JOIN ([School
Season
Division] AS [School Season Division_1] RIGHT JOIN Scores ON " & _
"([School Season Division_1].Home = Scores.Visitor)AND ([School Season
Division_1].Season = Scores.Season)) ON ([School Season
Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight


:

You appear to be missing a required space before the WHERE word, which
may
be the cause of your error:

Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"

The above should be

Scores.Season) AND ([School Season Division].Home = Scores.Home)" & _
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"


You said ' The txtSeason text box has values such as "1974 or 2007" '.
Do
you mean the text box would contain the value 1874, or the value 2007?
I
trust that you don't mean "1974 or 2007" as a literal string in the
textbox,
as that will not work in your query for the criterion.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Yes, I'm bulding SQL statement with VBA code. Essentially the
statement
below
is the code, but I've truncated out non relavant, but here it is
just
in
case. The txtSeason text box has values such as "1974 or 2007".


qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season,
[Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score],
Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS
[Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin]))))
AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin)
AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division -
Home],
+[School Season Division_1.Division] AS [School Season Division -
Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
"FROM Coefficients, [School Season Division] INNER JOIN ([School
Season
Division] AS [School Season Division_1] INNER JOIN Scores ON " & _
"([School Season Division_1].Home = Scores.Visitor)AND ([School
Season
Division_1].Season = Scores.Season)) ON ([School Season
Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)" &
_
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight


:

"Form-code"? Are you trying to build the SQL statement in VBA code?
Or
something else?

Post the code that you're using if that is what you're doing. Also,
what
types of values (show examples) will be in the txtSeason textbox.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Yes this worked for query, but it doesn't work when it is in a
form-code
(sorry I didn't mention that) such as :

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School
Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
"WHERE (((Scores.Season)= " & Me.txtSeason.Value & "));"

Erroor says "join function is not supported" - is there a work
around?
--
David McKnight


:

Try this:

SELECT Scores.Season, Scores.Visitor, Scores.Home, Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND ([School
Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



in
message
I have a query :

SELECT Scores.Season, Scores.Visitor, Scores.Home,
Scores.Margin
FROM [School Season Division] INNER JOIN ([School Season
Division]
AS
[School Season Division_1] INNER JOIN Scores ON ([School
Season
Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season
=
Scores.Season)) ON (Scores.Home = [School Season
Division].Home)
AND
([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

I would like to make it to make it a left or right join (not
sure
which)
on
season & home so that when there is missing data from the
[School
Season
Division] table I get a blank in the query for that
Click to expand...
Click to expand...
Click to expand...
 
That is very strange. Thanks for posting the workaround; I'm sure others
will find this useful at some point!
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



David McKnight said:
I found a work around.

Created a additional field in each table called [SeasonHome] which was
just
=[Season] &[Home] I was then able to right join these without any
problem.
--
David McKnight


Ken Snell said:
I don't know what else to suggest, sorry.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
Still no luck, no obvious syntax issues. Copied and pasted into SQL
view(removed " & _) Same result as before, with INNER JOIN works fine,
RIGHT
JOIN " JOIN expression not supported. Running Access 2007 saved in
Access
2007 format.
--
David McKnight


:

Add a step to your code to print out the SQL statement that you build:
Debug.Print qdf.SQL

take a look at the string and make sure that it's the correct syntax.
Post
it here if you don't see anything obvious.

Also, paste the printed-out SQL string into a new query (SQL View).
Will
it
run? Switch back to design view with it, then back to SQL view --
ACCESS
will "change" the statement to what it likes. Try using that syntax
for
building your SQL statement in code.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
I added space before WHERE but still get same error "JOIN function
not
supported". Works with INNER but not LEFT or RIGHT join.
Yes I did meam to say that txtSeason could have values such as 2007,
or
a
value such as 1974, etc.

qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season,
[Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score],
Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS
[Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS [Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin]))))
AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv Margin],Scores!Margin)
AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division -
Home],
+[School Season Division_1.Division] AS [School Season Division -
Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
" FROM Coefficients, [School Season Division] RIGHT JOIN ([School
Season
Division] AS [School Season Division_1] RIGHT JOIN Scores ON " & _
"([School Season Division_1].Home = Scores.Visitor)AND ([School
Season
Division_1].Season = Scores.Season)) ON ([School Season
Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)" &
_
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight


:

You appear to be missing a required space before the WHERE word,
which
may
be the cause of your error:

Scores.Season) AND ([School Season Division].Home = Scores.Home)" &
_
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"

The above should be

Scores.Season) AND ([School Season Division].Home = Scores.Home)" &
_
" WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"


You said ' The txtSeason text box has values such as "1974 or 2007"
'.
Do
you mean the text box would contain the value 1874, or the value
2007?
I
trust that you don't mean "1974 or 2007" as a literal string in the
textbox,
as that will not work in your query for the criterion.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



message
Yes, I'm bulding SQL statement with VBA code. Essentially the
statement
below
is the code, but I've truncated out non relavant, but here it is
just
in
case. The txtSeason text box has values such as "1974 or 2007".


qdf.SQL = "SELECT Scores.ID, Scores.Date, Scores.Season,
[Scores].[Season]+1
AS [Season-1], Scores.Visitor," & _
"Scores.[Visitor Score], Scores.Home, Scores.[Home Score],
Scores.Margin,
Scores.Nuetral, " & _
"Scores.Location, Scores.Location2, Scores.Line,
IIf(Scores!Margin=0,0,((Scores!Margin)/(Abs(Scores!Margin)))) AS
[Win-Loss],"
& _
"1+(([Home Score]+10)/([Home Score]+[Visitor Score]+20)) AS
[Percent
Margin], " & _
"IIf(Abs([Margin])=0,0,(Log(Abs([Margin])))*([Margin]/(Abs([Margin]))))
AS
[Adj Log Margin], IIf(Scores!Nuetral=0," & _
"Scores!Margin-Coefficients![Home Field Adv
Margin],Scores!Margin)
AS
[Adjusted Margin]," & _
"IIf(Scores!Nuetral=0,[Win-Loss]-Coefficients![Home Field
AdvWin-Loss],[Win-Loss]) AS [Adjusted Margin Win-Loss]," & _
"[School Season Division.Division] AS [School Season Division -
Home],
+[School Season Division_1.Division] AS [School Season Division -
Visitor], "
& _
"[Scores]![Date] &
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))" & _
"AS Game,
IIf(RTrim([Scores]![Home])<RTrim([Scores]![Visitor]),RTrim([Scores]![Home])
&
RTrim([Scores]![Visitor]),RTrim([Scores]![Visitor]) &
RTrim([Scores]![Home]))
AS Series, " & _
"Format([Scores].[Date],'ww') AS Week " & _
"FROM Coefficients, [School Season Division] INNER JOIN ([School
Season
Division] AS [School Season Division_1] INNER JOIN Scores ON " &
_
"([School Season Division_1].Home = Scores.Visitor)AND ([School
Season
Division_1].Season = Scores.Season)) ON ([School Season
Division].Season =
Scores.Season) AND ([School Season Division].Home = Scores.Home)"
&
_
"WHERE (((Scores.Season)>= " & Me.txtSeason.Value & " And
(Scores.Season)<=
" & Me.txtEndSeason.Value & "));"
--
David McKnight


:

"Form-code"? Are you trying to build the SQL statement in VBA
code?
Or
something else?

Post the code that you're using if that is what you're doing.
Also,
what
types of values (show examples) will be in the txtSeason
textbox.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



in
message
Yes this worked for query, but it doesn't work when it is in a
form-code
(sorry I didn't mention that) such as :

SELECT Scores.Season, Scores.Visitor, Scores.Home,
Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND
([School
Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
"WHERE (((Scores.Season)= " & Me.txtSeason.Value & "));"

Erroor says "join function is not supported" - is there a work
around?
--
David McKnight


:

Try this:

SELECT Scores.Season, Scores.Visitor, Scores.Home,
Scores.Margin
FROM [School Season Division]
RIGHT JOIN
([School Season Division] AS
[School Season Division_1]
RIGHT JOIN Scores
ON ([School Season Division_1].Home = Scores.Home) AND
([School
Season
Division_1].Season = Scores.Season))
ON (Scores.Home = [School Season Division].Home) AND ([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"David McKnight" <[email protected]>
wrote
in
message
I have a query :

SELECT Scores.Season, Scores.Visitor, Scores.Home,
Scores.Margin
FROM [School Season Division] INNER JOIN ([School Season
Division]
AS
[School Season Division_1] INNER JOIN Scores ON ([School
Season
Division_1].Home = Scores.Home) AND ([School Season
Division_1].Season
=
Scores.Season)) ON (Scores.Home = [School Season
Division].Home)
AND
([School
Season Division].Season = Scores.Season)
WHERE (((Scores.Season)>=2008 And (Scores.Season)<=2008));

I would like to make it to make it a left or right join
(not
sure
which)
on
season & home so that when there is missing data from the
[School
Season
Division] table I get a blank in the query for that
Click to expand...
Click to expand...
Click to expand...
 
Back
Top