Re: Help! Outer Join problem

On Aug 11, 11:01 am, John Spencer <spen...@xxxxxxxxx> wrote:
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



Lutingwrote:
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
On Aug 11, 9:09 am, John Spencer <spen...@xxxxxxxxx> wrote:
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

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 -

- Show quoted text -

Thank you, John. This works!
.

Relevant Pages

  • Re: strSQL using Like "*"
    ... so you need to craft the WHERE clause so that it does not compare to a field at all. ... Switch the query to SQL View, and edit the WHERE clause so it looks like this: ... A much more efficient solution is to create the filter string dynamically, in code, from the boxes where the user entered a value. ... to use a strSQL for the recordset and what I am querying on is based on 5 ...
    (microsoft.public.access.modulesdaovba)
  • Re: Build an IN clause from SELECT records
    ... string would be used for an IN clause in a subsequent SQL query. ... How do I return the results of this query to a string with each record ...
    (comp.databases.ms-access)
  • Re: issue with runing Select query with condition using code
    ... Build the whole query statement, not just the WHERE clause, and assign it like this: ... Dim strSql As String ... > Dim strsql As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: strSQL using Like "*"
    ... this can be odd the first time you see it. ... Ultimately a WHERE clause is something that evaluates to True or False If it's true, the record gets included; ... Switch the query to SQL View, and edit the WHERE clause so it looks like ... A much more efficient solution is to create the filter string dynamically, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)