Re: THANKS! -> Re: Valid MS Access SQL Syntax (Double JOINtedness)

From: Daniel Wilson (d.wilson_at_embtrak.com)
Date: 02/26/05 

Date: Sat, 26 Feb 2005 14:42:01 -0500

"Don" <someone@somewhere.net> wrote in message
news:eiU7UuCHFHA.1476@TK2MSFTNGP09.phx.gbl...
> Bob,
>
> Thanks for the comments! Your suggestion worked and I am now expanding my
> knowledge on the topic. As our ultimate goal is to host this on a SQL
> Server, I have had a non-Access mind set (hence my post to
> microsoft.public.sqlserver.programming). So much so I did not even think
> about using the Query Builder!
>
> Will the Access syntax work on SQL Server? It would be annoying to have
to
> come back and reconfigure the SQL strings. But I suppose sometimes such
is
> life!
>
> Thanks!
>
> Don
>
>
>
>
>
>
> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:eODjGECHFHA.3156@TK2MSFTNGP10.phx.gbl...
> > Don wrote:
> > > Is the following SQL valid in Access?
> > >
> > > SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status, P.Number,
> > > P.Type
> > > FROM Apartments AS A
> > > INNER JOIN Residents AS R
> > > ON R.AptNum = A.AptNum
> > > LEFT JOIN Phones AS P
> > > ON P.ResID = R.ResID
> > > AND P.Type = 'Work'
> > > WHERE A.Building = 3
> > > AND R.Status = 'Moved';
> > >
> > >
> > >
> > > When I try to run the query, I get the following error:
> > >
> > > Syntax error (missing operator) in query expression 'R.AptNum=
> > > A.AptNum LEFT JOIN Phones AS P ON P.ResID = R.ResID'.
> > >
> > > Any insight will be greatly appreciated!
> > >
> > > Thanks!
> > >
> > > Don
> >
> > Nothing to do with SQL Server. There is a group called
m.p.access.queries.
> >
> > JetSQL is fussy about nesting joins with parentheses when more than one
> join
> > is involved. The best way to build your query is to use the Access Query
> > Builder in Design View to create your joins, then switch to SQL View to
> > remove extraneous parentheses (except from the FROM clause) . It's been
a
> > while, but I think this will pass muster in Access:
> >
> > SELECT A.Building, A.AptNum, R.Name, R.AptNum, R.Status, P.Number,
> > P.Type
> > FROM Apartments AS A
> > INNER JOIN (Residents AS R
> > LEFT JOIN Phones AS P
> > ON P.ResID = R.ResID
> > AND P.Type = 'Work')
> > ON R.AptNum = A.AptNum
> > WHERE A.Building = 3
> > AND R.Status = 'Moved';
> >
> > Bob Barrows
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.
> >
> >
>

The other thing to remember on Access is that multiple OUTER JOINs are not
permitted in a single query. When those are necessary, you must build
stored sub-queries and join them again in your master query. 

-- 
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company

Relevant Pages

  • Re: THANKS! -> Re: Valid MS Access SQL Syntax (Double JOINtedness)
    ... As our ultimate goal is to host this on a SQL ... > Will the Access syntax work on SQL Server? ... The best way to build your query is to use the Access Query ... >> Bob Barrows ...
    (microsoft.public.sqlserver.programming)
  • Re: ntext getting truncated
    ... Tibor Karaszi, SQL Server MVP ... I applied the query as follows: ... select counthowmany, datalengthntextlength ...
    (microsoft.public.sqlserver.programming)
  • Re: upsizing to sql 2005
    ... which include MS SQL Server linked table. ... Query Name: Arcadia - ARC ... are still unable to upsize one of these queries, ...
    (microsoft.public.access.queries)
  • Re: VS.NET 2003, ADO.NET 1.1 and Access 2000: Getting a Concurrency violation
    ... I am using an Access update query, with parameters, for the ... Then my app goes against a SQL ... OleDbDataAdapter that I use to retrieve the original table from the ... So your named parameters for SQL Server suddenly become anonymous ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: upsizing to sql 2005
    ... A query in Access-Jet will be started and under the supervision of Jet, ... Jet syntax parsing and evaluation. ... so, in this case, into MS SQL Server. ...
    (microsoft.public.access.queries)
Loading