Join deleted in Design view

[Ennex]

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);

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

 

[Chris2]

Hello Experts,

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?

Ennex,

MS Access' Query Design Grid just can't display all possible
configurations of the underlying SQL.

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.

I opened a new Query, switched to SQL view, saved the Query (it saved)
while still in SQL view, and then closed the Query while still in SQL
View. Now, if Access 2003 behaves like Access 2000 in this regard,
when selecting "Design View" to open the Query again, since it was
last closed in SQL View, it will open again in SQL View.

I was able to open and close it without problems, several times.

I then added columns to the SELECT clause, and that did not cause any
problems.

Essentially, as far as this particular Query goes, just operating in
SQL View from now on.


Sincerely,

Chris O.