Query Designer and outer joins

From: sqlvs (sqlvs_at_myway.com)
Date: 04/13/04 

Date: 13 Apr 2004 08:32:33 -0700

Hello,

Are there any issues with Access 2000 and outer joins in the query
designer?

I'm (unfortunately) using Access to report against a SQL Server db,
and started with a valid SQL Server query similar to the following:

SELECT (cols)
FROM Table1
INNER JOIN etc.
INNER JOIN etc.
LEFT OUTER JOIN etc.
LEFT OUTER JOIN etc.

IOW, I can string a bunch of these joins together and SQL Server knows
what to do. But I've struck out after converting to Access. First I
tried hand-coding the query, but read about some issues with nesting
and parentheses so I decided to just use the Query Designer, since it
should know how to produce the right code, right?

Wrong. I need to join nine tables, and am joining them one-by-one
until I get to a point where no more records are being returned. This
should never happen, and it isn't even consistent. I can then remove
the last table from the Query Designer, and my records are back. I
know there are valid records in the joins, and it isn't even
consistently happening on the same table.

It's like once it gets to the fifth or sixth join, the syntax it's
producing no longer works. But it only seems to be with a mix of inner
and outer joins.

Does the Query Designer really "break"? I'm not doing anything
unreasonable.

What's the right way to do multiple joins, with a mix of inner and
outer, in the Query Designer? I have to get this to work.

Your help would be appreciated! :)

Relevant Pages

  • Re: Problem using Access or Query Designer to run queries in SQL Serve
    ... >or Query Designer within Enterprise Manager, it works and I get data back. ... >ODBC Call Failed [ODBC SQL Server Driver] Timeout Expirederror in Access ... >[ODBC SQL Server Driver] Timeout Expired ...
    (microsoft.public.sqlserver.odbc)
  • Re: Can someone help me with multiple "Left Outer Joins"?
    ... > Using three "Left Outer Joins" slows the system down considerably. ... tables have indexes on the columns you join on, I would expect the query ... could hit multiple rows in the latter tables. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: parameter in sql query
    ... Most likely designer omits '@' in parameter name. ... Desktop provider would silently add it, ... >> Does the parameter substitution occur after or before the query is sent ... >> I tried writing some queries to the SQL server but couldn't reproduce ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Enterprise Manager confusion
    ... I orginally started out in MS Access, so when I began using SQL Server, I ... it does like the Access query builder. ... By the way, you can drag columns individually in QA, you don't have to drag ... SOME others out there who feel, as I do, that the View designer is far ...
    (microsoft.public.sqlserver.tools)
  • Re: Cant double click joins in VFP8 view designer
    ... or does the view designer still blow chunks? ... mixes left inner joins with left outer joins. ... I can create it in the query designer and run ...
    (microsoft.public.fox.programmer.exchange)
Quantcast