Help! Outer Join problem

L

Luting

I have an inner join query that works fine. But I want to make it
outer join, so I changed the "inner" to "left". It doesn't work! Here
is the query with problem:

SELECT PROD_RESULT.LOT_CREATED AS LOT_CREATED, TAs.[TA ABBRV] AS
TA_ON_SHIFT, Right(PROD_RESULT.VENDOR_LOT_NO,1) AS EVAP, "1" AS
BARREL, PROD_RESULT.TRANS_QTY AS WEIGHT,
IIf(EVAP<"D","NORTH","SOUTH")
AS PLANT, "R" & PROD_RESULT.LOT_NO & "001001" AS LOTID, "001" AS LOT,
"001" AS SUB,
IIf(EVAP<"D",OPER_TEAM_RANGE.OPERATOR,OPER_TEAM_RANGE.OPERATOR2) AS
OPER, Val(Left(PROD_RESULT.VENDOR_LOT_NO,4)) AS RUN,
Right(PROD_RESULT.ITEM_NO,3) AS TYPE, OPER_TEAM_RANGE.TEAM AS TEAM


FROM OPER_TEAM_RANGE LEFT JOIN TAs ON OPER_TEAM_RANGE.TA=TAs.TA,
PROD_RESULT INNER JOIN RMCS_TRANS_ALL ON
(PROD_RESULT.LOT_NO=RMCS_TRANS_ALL.LOT_NO) AND
(PROD_RESULT.SUBLOT_NO=RMCS_TRANS_ALL.SUBLOT_NO)


WHERE (((PROD_RESULT.LOT_CREATED)<Forms!Rework_Update!endtxtField And
(PROD_RESULT.LOT_CREATED)>Forms!Rework_Update!begintxtField) And
((RMCS_TRANS_ALL.RMCS_FUNCTION)="RF - RI")) And
PROD_RESULT.LOT_CREATED>FORMAT(OPER_TEAM_RANGE.BEGIN,"MM/DD/YYYY
HH:NN:SS") And
PROD_RESULT.LOT_CREATED<FORMAT(OPER_TEAM_RANGE.END,"MM/
DD/YYYY HH:NN:SS")
ORDER BY PROD_RESULT.LOT_CREATED;
 
J

Jeff Boyce

You've described "how" but not "why".

What is it about an outer join that you think will help you solve your
issue? ?!what issue?!

It all starts with the data ... can you describe the data and relationships
you're using?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

John Spencer

What do you mean by "It doesn't work!"?

Are you getting the wrong results, no results, a syntax error message or some
other problem?

I notice that you are attempting to use a cartesian join - that is one set of
tables is not joined to another set of tables

FROM (OPER_TEAM_RANGE
LEFT JOIN TAs
ON OPER_TEAM_RANGE.TA=TAs.TA),

(PROD_RESULT INNER JOIN RMCS_TRANS_ALL
ON (PROD_RESULT.LOT_NO=RMCS_TRANS_ALL.LOT_NO) AND
(PROD_RESULT.SUBLOT_NO=RMCS_TRANS_ALL.SUBLOT_NO))

In addition, I'm not sure why you are changing your dates to formatted strings
in the where clause.

PROD_RESULT.LOT_CREATED>FORMAT(OPER_TEAM_RANGE.BEGIN,"MM/DD/YYYY HH:NN:SS")

If Lot_Created is a DateTime field and Begin is a DateTime field, this
comparison will give you erroneous results (if it works at all).


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
L

Luting

Hi Jonh,

Sorry I didn't state it clearly.
Access gives me the alert saying "Join statement is not supported"
when I tried to save the query.

Yes, the tables should be joined into two sets. And the two set is
actually "connected" by the WHERE clause:

PROD_RESULT.LOT_CREATED>FORMAT(OPER_TEAM_RANGE.BEGIN,"MM/DD/YYYY
HH:NN:SS")
And
PROD_RESULT.LOT_CREATED<FORMAT(OPER_TEAM_RANGE.END,"MM/DD/YYYY
HH:NN:SS")

It's not an EQUAL statement so I can't add it into the JOIN statement.
You are right the Lot.created field and begin/end fields are all Date/
Time type. But why do you think it will give me erroneous results?
What kind of erroneous results? The result seems to be correct when I
didn't change "inner join" to "left join". But maybe I didn't notice
the problem.


Luting
What do you mean by "It doesn't work!"?

Are you getting the wrong results, no results, a syntax error message or some
other problem?

I notice that you are attempting to use a cartesian join - that is one set of
tables is not joined to another set of tables

FROM (OPER_TEAM_RANGE
LEFT JOIN TAs
ON OPER_TEAM_RANGE.TA=TAs.TA),

(PROD_RESULT INNER JOIN RMCS_TRANS_ALL
ON (PROD_RESULT.LOT_NO=RMCS_TRANS_ALL.LOT_NO) AND
(PROD_RESULT.SUBLOT_NO=RMCS_TRANS_ALL.SUBLOT_NO))

In addition, I'm not sure why you are changing your dates to formatted strings
in the where clause.

PROD_RESULT.LOT_CREATED>FORMAT(OPER_TEAM_RANGE.BEGIN,"MM/DD/YYYY HH:NN:SS")

If Lot_Created is a DateTime field and Begin is a DateTime field, this
comparison will give you erroneous results (if it works at all).

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County


I have an inner join query that works fine. But I want to make it
outer join, so I changed the "inner" to "left". It doesn't work! Here
is the query with problem:
Click to expand...
SELECT PROD_RESULT.LOT_CREATED AS LOT_CREATED, TAs.[TA ABBRV] AS
TA_ON_SHIFT, Right(PROD_RESULT.VENDOR_LOT_NO,1) AS EVAP, "1" AS
BARREL, PROD_RESULT.TRANS_QTY AS WEIGHT,
IIf(EVAP<"D","NORTH","SOUTH")
AS PLANT, "R" & PROD_RESULT.LOT_NO & "001001" AS LOTID, "001" AS LOT,
"001" AS SUB,
IIf(EVAP<"D",OPER_TEAM_RANGE.OPERATOR,OPER_TEAM_RANGE.OPERATOR2) AS
OPER, Val(Left(PROD_RESULT.VENDOR_LOT_NO,4)) AS RUN,
Right(PROD_RESULT.ITEM_NO,3) AS TYPE, OPER_TEAM_RANGE.TEAM AS TEAM
Click to expand...
FROM OPER_TEAM_RANGE LEFT JOIN TAs ON OPER_TEAM_RANGE.TA=TAs.TA,
PROD_RESULT INNER JOIN RMCS_TRANS_ALL ON
(PROD_RESULT.LOT_NO=RMCS_TRANS_ALL.LOT_NO) AND
(PROD_RESULT.SUBLOT_NO=RMCS_TRANS_ALL.SUBLOT_NO)
Click to expand...
WHERE (((PROD_RESULT.LOT_CREATED)<Forms!Rework_Update!endtxtField And
(PROD_RESULT.LOT_CREATED)>Forms!Rework_Update!begintxtField) And
((RMCS_TRANS_ALL.RMCS_FUNCTION)="RF - RI")) And
PROD_RESULT.LOT_CREATED>FORMAT(OPER_TEAM_RANGE.BEGIN,"MM/DD/YYYY
HH:NN:SS") And
PROD_RESULT.LOT_CREATED<FORMAT(OPER_TEAM_RANGE.END,"MM/
DD/YYYY HH:NN:SS")
ORDER BY PROD_RESULT.LOT_CREATED;- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
L

Luting

Hi Jeff,

I used four tables here:
1.PROD_RESULT
2.RMCS_TRANS_ALL
3.OPER_TEAM_RANGE
4. TAs

Table 1 & 2 together will provide information of the production
problems that're recorded in the database.
Table 1 and 2 are connected by the product lot number and form the
dataset 1

Table 3 contains information of which production team is in charge on
which shift.
Table 4 is needed here only for translating the team advisor's names
in Table 3 to an abbreviation.
Table 3 and 4 are connected by the TA field, which stands for Team
Adivisor 's full names.
This is dataset 2.

The datasets are connected by the statement:
PROD_RESULT.LOT_CREATED>FORMAT(OPER_TEAM_RANGE.BEGIN,"MM/DD/YYYY
HH:NN:SS")
And
PROD_RESULT.LOT_CREATED<FORMAT(OPER_TEAM_RANGE.END,"MM/DD/YYYY
HH:NN:SS")

Hope it's clearer this time.
Luting

You've described "how" but not "why".

What is it about an outer join that you think will help you solve your
issue?  ?!what issue?!

It all starts with the data ... can you describe the data and relationships
you're using?

Regards

Jeff Boyce
Microsoft Office/Access MVP




I have an inner join query that works fine. But I want to make it
outer join, so I changed the "inner" to "left". It doesn't work! Here
is the query with problem:
Click to expand...
SELECT PROD_RESULT.LOT_CREATED AS LOT_CREATED, TAs.[TA ABBRV] AS
TA_ON_SHIFT, Right(PROD_RESULT.VENDOR_LOT_NO,1) AS EVAP, "1" AS
BARREL, PROD_RESULT.TRANS_QTY AS WEIGHT,
IIf(EVAP<"D","NORTH","SOUTH")
AS PLANT, "R" & PROD_RESULT.LOT_NO & "001001" AS LOTID, "001" AS LOT,
"001" AS SUB,
IIf(EVAP<"D",OPER_TEAM_RANGE.OPERATOR,OPER_TEAM_RANGE.OPERATOR2) AS
OPER, Val(Left(PROD_RESULT.VENDOR_LOT_NO,4)) AS RUN,
Right(PROD_RESULT.ITEM_NO,3) AS TYPE, OPER_TEAM_RANGE.TEAM AS TEAM
Click to expand...
FROM OPER_TEAM_RANGE LEFT JOIN TAs ON OPER_TEAM_RANGE.TA=TAs.TA,
PROD_RESULT INNER JOIN RMCS_TRANS_ALL ON
(PROD_RESULT.LOT_NO=RMCS_TRANS_ALL.LOT_NO) AND
(PROD_RESULT.SUBLOT_NO=RMCS_TRANS_ALL.SUBLOT_NO)
Click to expand...
WHERE (((PROD_RESULT.LOT_CREATED)<Forms!Rework_Update!endtxtField And
(PROD_RESULT.LOT_CREATED)>Forms!Rework_Update!begintxtField) And
((RMCS_TRANS_ALL.RMCS_FUNCTION)="RF - RI")) And
PROD_RESULT.LOT_CREATED>FORMAT(OPER_TEAM_RANGE.BEGIN,"MM/DD/YYYY
HH:NN:SS") And
PROD_RESULT.LOT_CREATED<FORMAT(OPER_TEAM_RANGE.END,"MM/
DD/YYYY HH:NN:SS")
ORDER BY PROD_RESULT.LOT_CREATED;- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
J

John Spencer

Well. looking at it again and knowing the fields are all dateTime, you may not
run into the problem. The string MIGHT just be implicitly converted back to a
dateTime type. If not, you could end up with a problem of string comparisons.

For instance, as a STRING
09/13/2008 is greater than 08/01/2009 and
09/13/2008 is less than 12/01/2007
as dateTime fields the opposite is true.

You can try doing this in two queries. Do the LEFT JOIN portion as a separate
query and then bring that into another query with the other two tables.

Try the following as the FROM clause (and you should be able to drop the
criteria in the WHERE clause for the date fields.

I say TRY as this may or may not work.

FROM ((OPER_TEAM_RANGE As O LEFT JOIN TAs As T
ON O.TA=T.TA) INNER JOIN
PROD_RESULT AS P
ON P.Lot_Created > 0.Begin AND
P.Lot_Create < O.End)
INNER JOIN RMCS_TRANS_ALL AS R
ON P.LOT_NO=R.LOT_NO AND
P.SUBLOT_NO=R.SUBLOT_NO

You will notice that I used one-letter aliases for the table names. It makes
typing easier and reading easier for me to do so.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi Jonh,

Sorry I didn't state it clearly.
Access gives me the alert saying "Join statement is not supported"
when I tried to save the query.

Yes, the tables should be joined into two sets. And the two set is
actually "connected" by the WHERE clause:

PROD_RESULT.LOT_CREATED>FORMAT(OPER_TEAM_RANGE.BEGIN,"MM/DD/YYYY
HH:NN:SS")
And
PROD_RESULT.LOT_CREATED<FORMAT(OPER_TEAM_RANGE.END,"MM/DD/YYYY
HH:NN:SS")

It's not an EQUAL statement so I can't add it into the JOIN statement.
You are right the Lot.created field and begin/end fields are all Date/
Time type. But why do you think it will give me erroneous results?
What kind of erroneous results? The result seems to be correct when I
didn't change "inner join" to "left join". But maybe I didn't notice
the problem.


Luting
What do you mean by "It doesn't work!"?

Are you getting the wrong results, no results, a syntax error message or some
other problem?

I notice that you are attempting to use a cartesian join - that is one set of
tables is not joined to another set of tables

FROM (OPER_TEAM_RANGE
LEFT JOIN TAs
ON OPER_TEAM_RANGE.TA=TAs.TA),

(PROD_RESULT INNER JOIN RMCS_TRANS_ALL
ON (PROD_RESULT.LOT_NO=RMCS_TRANS_ALL.LOT_NO) AND
(PROD_RESULT.SUBLOT_NO=RMCS_TRANS_ALL.SUBLOT_NO))

In addition, I'm not sure why you are changing your dates to formatted strings
in the where clause.

PROD_RESULT.LOT_CREATED>FORMAT(OPER_TEAM_RANGE.BEGIN,"MM/DD/YYYY HH:NN:SS")

If Lot_Created is a DateTime field and Begin is a DateTime field, this
comparison will give you erroneous results (if it works at all).

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County


I have an inner join query that works fine. But I want to make it
outer join, so I changed the "inner" to "left". It doesn't work! Here
is the query with problem:
SELECT PROD_RESULT.LOT_CREATED AS LOT_CREATED, TAs.[TA ABBRV] AS
TA_ON_SHIFT, Right(PROD_RESULT.VENDOR_LOT_NO,1) AS EVAP, "1" AS
BARREL, PROD_RESULT.TRANS_QTY AS WEIGHT,
IIf(EVAP<"D","NORTH","SOUTH")
AS PLANT, "R" & PROD_RESULT.LOT_NO & "001001" AS LOTID, "001" AS LOT,
"001" AS SUB,
IIf(EVAP<"D",OPER_TEAM_RANGE.OPERATOR,OPER_TEAM_RANGE.OPERATOR2) AS
OPER, Val(Left(PROD_RESULT.VENDOR_LOT_NO,4)) AS RUN,
Right(PROD_RESULT.ITEM_NO,3) AS TYPE, OPER_TEAM_RANGE.TEAM AS TEAM
FROM OPER_TEAM_RANGE LEFT JOIN TAs ON OPER_TEAM_RANGE.TA=TAs.TA,
PROD_RESULT INNER JOIN RMCS_TRANS_ALL ON
(PROD_RESULT.LOT_NO=RMCS_TRANS_ALL.LOT_NO) AND
(PROD_RESULT.SUBLOT_NO=RMCS_TRANS_ALL.SUBLOT_NO)
WHERE (((PROD_RESULT.LOT_CREATED)<Forms!Rework_Update!endtxtField And
(PROD_RESULT.LOT_CREATED)>Forms!Rework_Update!begintxtField) And
((RMCS_TRANS_ALL.RMCS_FUNCTION)="RF - RI")) And
PROD_RESULT.LOT_CREATED>FORMAT(OPER_TEAM_RANGE.BEGIN,"MM/DD/YYYY
HH:NN:SS") And
PROD_RESULT.LOT_CREATED<FORMAT(OPER_TEAM_RANGE.END,"MM/
DD/YYYY HH:NN:SS")
ORDER BY PROD_RESULT.LOT_CREATED;- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
Click to expand...
 
L

Luting

Well. looking at it again and knowing the fields are all dateTime, you may not
run into the problem.  The string MIGHT just be implicitly converted back to a
dateTime type.  If not, you could end up with a problem of string comparisons.

For instance, as a STRING
  09/13/2008 is greater than 08/01/2009 and
  09/13/2008 is less than 12/01/2007
as dateTime fields the opposite is true.

You can try doing this in two queries.  Do the LEFT JOIN portion as a separate
query and then bring that into another query with the other two tables.

Try the following as the FROM clause (and you should be able to drop the
criteria in the WHERE clause for the date fields.

I say TRY as this may or may not work.

FROM ((OPER_TEAM_RANGE As O LEFT JOIN TAs As T
   ON O.TA=T.TA) INNER JOIN
    PROD_RESULT AS P
   ON P.Lot_Created > 0.Begin AND
      P.Lot_Create < O.End)
   INNER JOIN RMCS_TRANS_ALL AS R
    ON P.LOT_NO=R.LOT_NO AND
    P.SUBLOT_NO=R.SUBLOT_NO

You will notice that I used one-letter aliases for the table names.  Itmakes
typing easier and reading easier for me to do so.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County



Lutingwrote:
Hi Jonh,
Click to expand...
Sorry I didn't state it clearly.
Access gives me the alert saying "Join statement is not supported"
when I tried to save the query.
Click to expand...
Yes, the tables should be joined into two sets. And the two set is
actually "connected" by the WHERE clause:

It's not an EQUAL statement so I can't add it into the JOIN statement.
You are right the Lot.created field and begin/end fields are all Date/
Time type. But why do you think it will give me erroneous results?
What kind of erroneous results? The result seems to be correct when I
didn't change "inner join" to "left join". But maybe I didn't notice
the problem.
Click to expand...
Luting
What do you mean by "It doesn't work!"?
Are you getting the wrong results, no results, a syntax error message or some
other problem?
I notice that you are attempting to use a cartesian join - that is oneset of
tables is not joined to another set of tables
FROM (OPER_TEAM_RANGE
LEFT JOIN TAs
ON OPER_TEAM_RANGE.TA=TAs.TA),
(PROD_RESULT INNER JOIN RMCS_TRANS_ALL
ON (PROD_RESULT.LOT_NO=RMCS_TRANS_ALL.LOT_NO) AND
(PROD_RESULT.SUBLOT_NO=RMCS_TRANS_ALL.SUBLOT_NO))
In addition, I'm not sure why you are changing your dates to formattedstrings
in the where clause.
PROD_RESULT.LOT_CREATED>FORMAT(OPER_TEAM_RANGE.BEGIN,"MM/DD/YYYY HH:NN:SS")
If Lot_Created is a DateTime field and Begin is a DateTime field, this
comparison will give you erroneous results (if it works at all).
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Lutingwrote:
I have an inner join query that works fine. But I want to make it
outer join, so I changed the "inner" to "left". It doesn't work! Here
is the query with problem:
SELECT PROD_RESULT.LOT_CREATED AS LOT_CREATED, TAs.[TA ABBRV] AS
TA_ON_SHIFT, Right(PROD_RESULT.VENDOR_LOT_NO,1) AS EVAP, "1" AS
BARREL, PROD_RESULT.TRANS_QTY AS WEIGHT,
IIf(EVAP<"D","NORTH","SOUTH")
AS PLANT, "R" & PROD_RESULT.LOT_NO & "001001" AS LOTID, "001" AS LOT,
"001" AS SUB,
IIf(EVAP<"D",OPER_TEAM_RANGE.OPERATOR,OPER_TEAM_RANGE.OPERATOR2) AS
OPER, Val(Left(PROD_RESULT.VENDOR_LOT_NO,4)) AS RUN,
Right(PROD_RESULT.ITEM_NO,3) AS TYPE, OPER_TEAM_RANGE.TEAM AS TEAM
FROM OPER_TEAM_RANGE LEFT JOIN TAs ON OPER_TEAM_RANGE.TA=TAs.TA,
PROD_RESULT INNER JOIN RMCS_TRANS_ALL ON
(PROD_RESULT.LOT_NO=RMCS_TRANS_ALL.LOT_NO) AND
(PROD_RESULT.SUBLOT_NO=RMCS_TRANS_ALL.SUBLOT_NO)
WHERE (((PROD_RESULT.LOT_CREATED)<Forms!Rework_Update!endtxtField And
(PROD_RESULT.LOT_CREATED)>Forms!Rework_Update!begintxtField) And
((RMCS_TRANS_ALL.RMCS_FUNCTION)="RF - RI")) And
PROD_RESULT.LOT_CREATED>FORMAT(OPER_TEAM_RANGE.BEGIN,"MM/DD/YYYY
HH:NN:SS") And
PROD_RESULT.LOT_CREATED<FORMAT(OPER_TEAM_RANGE.END,"MM/
DD/YYYY HH:NN:SS")
ORDER BY PROD_RESULT.LOT_CREATED;- Hide quoted text -
- Show quoted text -- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...

Thank you, John. This works!
 
Top