HOWTO: Use conditions in FROM clause in Access sintax.

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?

TIA,

Nekane.




.

Relevant Pages

  • Extremely slow IN clause
    ... I have an updateable query, Query1, that includes a join between two tables: ... The ID field is the PK of Table2. ... returns over 4500 records in less than a second; adding the IN clause ...
    (microsoft.public.access.queries)
  • Re: HOWTO: Use conditions in FROM clause in Access sintax.
    ... Access query. ... is another valid way to write this condition in the FROM clause. ... 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)