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

Hi,

Thanks for your quick answer but I am afraid that the select you have wrote
doesn't give the same results as the one that I wrote with the condition of
Table2.Data='data3' in FROM clause. If you execute them in SQL Server I
mean.

If you execute your select with the condition in the WHERE clause you only
get one row, that woth Table2.Data = 'data3' . But it is a LEFT OUTER JOIN
and that means that I need all rows in Table1 and only matching one in
Table2, so I would get 7 rows. 6 of them with null in Table2.Data field and
another one with data3.

This is the result I get in SQL Server, but I can't in Access because of
some kind of syntactic problem.

Any ideas?

Thank you again,

Nekane.
"MGFoster" <me@xxxxxxxxxxx> escribió en el mensaje
news:dgITe.7487$Wd7.5466@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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