S
sqlvs
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!
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!