Is this join valid?

Hello Experts,

Thank you for the help the other day on stored queries and work tables.

Now I've got a mysterious problem. It cropped up in a DB with a complex set
of tables and queries, but I've boiled down the behavior to appear in the
following simpler structure:

SELECT ProductData.data2
FROM ((ProductLines INNER JOIN LineData ON ProductLines.ID = LineData.LineID)
INNER JOIN Products
ON ProductLines.ID = Products.LineID)
INNER JOIN ProductData
ON (Products.ID = ProductData.ProdID)
AND (ProductLines.ID = ProductData.LineID);

This query gives me a problem both in Access and in SQL running on an ASP
page in JScript. But I've studied the behavior more extensively in Access, so
that's what I'll describe here.

The joins in this query are all defined in Relationships with referential
integrity and cascade updating. So the joins show up automatically when the
tables are added to a blank query in Design view. (In addition to the defined
relationships, when the tables are added in Design view, links also pop up
between the field LineID in the table Products and the same-name fields in
the tables LineData and ProductData. I delete those links.) I saved the above
code from the SQL view after creating the query in Design View.

Now here's the problem. If I close the query, then reopen it in Design view
and make any change in the fields SELECTed, such as adding the field
ProductData.LineID, then close the query, when I then reopen it in Design
view again, I get the following message:

"MS Office Access can't represent the join expression
Products.ID=ProductData.ProdID in Design view." and the message box lists
three possible causes, a deleted or renamed field, a misspelled field name or
table name in the join expression, or an unsupported operator (such as > or
<) in the join, none of which apply. The message box has only an Okay button
and when I click it, the indicated join is gone. The join is still there in
Relationships; it's only gone in the query. And without that that join the
query brings up unwanted combinations of records.

Can anyone see why Design view chokes on that join? And why does that only
happen after reopening the query with changes? Is there something invalid
about the join that is not caught in Relationships, and is not caught when
initially running the query, but is caught after making a change to it?

If the join is valid, it's going to be hard to work on improvements to this
query if Design view is going to kill that join every time I make a change to
it.

Thank you if you have any insights for me.

Regards,
Marshall Burns
www.Ennex.com

Re: Is this join valid?

Ennex wrote:
> Hello Experts,
>
> Thank you for the help the other day on stored queries and work
> tables.
>
> Now I've got a mysterious problem. It cropped up in a DB with a
> complex set of tables and queries, but I've boiled down the behavior
> to appear in the following simpler structure:
>
> SELECT ProductData.data2
> FROM ((ProductLines INNER JOIN LineData ON ProductLines.ID =
> LineData.LineID) INNER JOIN Products
> ON ProductLines.ID = Products.LineID)
> INNER JOIN ProductData
> ON (Products.ID = ProductData.ProdID)
> AND (ProductLines.ID = ProductData.LineID);
>
> This query gives me a problem both in Access and in SQL running on an
> ASP page in JScript.

Below, you describe a problem with the Design View that really should not
affect what happens when running a query from ASP. Som what are the symptoms
you experience when running this query in ASP?

It would help if you provided DDL and sample data to help us help you. See:
www.aspfaq.com/5006

> But I've studied the behavior more extensively
> in Access, so that's what I'll describe here.
>
> The joins in this query are all defined in Relationships with
> referential integrity and cascade updating. So the joins show up
> automatically when the tables are added to a blank query in Design
> view. (In addition to the defined relationships, when the tables are
> added in Design view, links also pop up between the field LineID in
> the table Products and the same-name fields in the tables LineData
> and ProductData. I delete those links.) I saved the above code from
> the SQL view after creating the query in Design View.
>
> Now here's the problem. If I close the query, then reopen it in
> Design view and make any change in the fields SELECTed, such as
> adding the field ProductData.LineID, then close the query, when I
> then reopen it in Design view again, I get the following message:
>
> "MS Office Access can't represent the join expression
> Products.ID=ProductData.ProdID in Design view." and the message box
> lists three possible causes, a deleted or renamed field, a misspelled
> field name or table name in the join expression, or an unsupported
> operator (such as > or <) in the join, none of which apply. The
> message box has only an Okay button and when I click it, the
> indicated join is gone. The join is still there in Relationships;
> it's only gone in the query. And without that that join the query
> brings up unwanted combinations of records.
>
> Can anyone see why Design view chokes on that join? And why does that
> only happen after reopening the query with changes? Is there
> something invalid about the join that is not caught in Relationships,
> and is not caught when initially running the query, but is caught
> after making a change to it?
>
> If the join is valid, it's going to be hard to work on improvements
> to this query if Design view is going to kill that join every time I
> make a change to it.
>
> Thank you if you have any insights for me.
>

You would have gotten a quicker response from an Access newsgroup. I have
not worked with Access for years, but from what I can recall, the Design
View does not support the display of some types of joins that actually are
valid joins. The queries would run well when built in SQL View, but
switching to Design View would cause the behavior you describe. There were
several queries I created back then that I had to avoid switching to Design
View for this very reason. I had to make all changes to these queries in SQL
View. If you need a more detailed answer as to why this is the case, I
suggest posting to one of the Access groups.

I really don't see a problem with your join statement above. Perhaps it's a
datatype issue. ISTR that the Design view chokes when fields with different
data types are used in a join. But my mermory is definitely hazy on this
point.

More to the point, I see nothing in the above sql that would cause a problem
in SQL Server.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Is this join valid?

"Ennex" wrote in message
news:72825944-1559-499E-B71E-23AA7E425B6D@microsoft.com...

> SELECT ProductData.data2
> FROM ((ProductLines INNER JOIN LineData ON ProductLines.ID =
LineData.LineID)
> INNER JOIN Products
> ON ProductLines.ID = Products.LineID)
> INNER JOIN ProductData
> ON (Products.ID = ProductData.ProdID)
> AND (ProductLines.ID = ProductData.LineID);


> "MS Office Access can't represent the join expression
> Products.ID=ProductData.ProdID in Design view."

I think that's just one of those "features" of design view,
it's saying that it can't show you the join graphically -
not that the join won't work.

However, you may find that you can do without the
offending line, and still get the same results.

The feature I find more annoying is when the parentheses
around a subquery are changed to "[" and "]."


--
roger