Re: HOWTO: Use conditions in FROM clause in Access sintax.

NKN wrote: 
Hello all,

I am triying to use condition wtih some fields in the FROM clause of an
Access query. But I get an error saying that "the conbination expresion is
not valid".

I know that this kind of sintax is valid in SQL Server but I ignore if there
is another valid way to write this condition in the FROM clause. Does
anybody know how to do it?

I am using 2 simple tables donde in Access. Table1 and Table2.

Table1 has 2 fields. Table2 has 3 fields. The 2 fields in Table1 are related
to the first 2 fields in Table2. I need to make a LEFT JOIN to get all
records in Table1 but only the record that matches the condition in Table2.
This is the query I'm triying to execute.

SELECT Table1.Ref, Table1.RefTwo, Table2.Data
FROM Table1 LEFT OUTER JOIN Table2 ON (Table1.RefTwo= Table2.RefTwo) AND
(Table1.Ref = Table2.RefExt) AND (Table2.Data = 'data3');

This are the data I am using.

  Table1 Ref     RefTwo
      ref1 ref11
      ref2 ref21
      ref3 ref31
      ref4 ref41
      ref5 ref51
      ref6 ref61
      ref7 ref71
      ref8 ref81
      ref9 ref91


  Table2 Ref    RefExt   RefTwo   Data
      1 ref2 ref21 data1
      2 ref2 ref21 data2
      3 ref3 ref31 data3
      4 ref4 ref41 data4
      5 ref5 ref51 data5
      6 ref5 ref51 data6
      7 ref5 ref51 data7


I need to get this result: Only teh record in Table2 that matches the
condition. The rest are not in the result because are NULL.
  Result  RefExt   RefTwo   Data
     ref1 ref21
     ref2 ref21
     ref3 ref31 data3
     ref4 ref41
     ref5 ref51
     ref6 ref61
     ref7 ref71



This is the result I get in SQL Server if I execute that query. Is it
possible to do it in Access with the conditions in FROM?


Like this:

SELECT Table1.Ref, Table1.RefTwo, Table2.Data
FROM Table1 LEFT JOIN Table2
  ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt)
WHERE (Table2.Data = 'data3')

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
.

Relevant Pages

  • HOWTO: Use conditions in FROM clause in Access sintax.
    ... Access query. ... is another valid way to write this condition in the FROM clause. ... records in Table1 but only the record that matches the condition in Table2. ... This is the query I'm triying to execute. ...
    (microsoft.public.access.queries)
  • Re: Problem getting all Access records to merge into Word.
    ... Does your Access query have any date literals in it, ... WHERE mydate < #1/1/2007# ... I would try a version of the query that uses datevalue ...
    (microsoft.public.word.mailmerge.fields)
  • RE: Any good T-SQL quick reference recommended?
    ... The full syntax of the SELECT ... SELECT Clause ... Specifies the columns to be returned by the query. ... Specifies that duplicate rows can appear in the result set. ...
    (microsoft.public.sqlserver.programming)
  • RE: Xlocking with a select statement
    ... named query expression, order clause, update clause, lock option ... A result table or the underlying base tables are updateable if the query ... A lock can be requested for the ...
    (microsoft.public.sqlserver.programming)
  • Re: update query: still having problems
    ... "Michel Walsh" wrote: ... From the User Interface, in the toolbar, or the menu, when you edit a query, ... If this is what you want, fine, else, add a WHERE clause to limit ... SELECT Department.*, sheet1.* ...
    (microsoft.public.access.queries)
Loading