Sort button’s dual functionality bug confirmed.

A

Afzal

I ran into this issue when query result would change when I clicked on the
Sort button for an INNER JOIN query (or any query) on two tables, CUSTOMERS
and ORDERDS, with Many-to-Many relationship. see "Wrong result from INNER
JOIN query if not ..." dated 3/30/2004.

This is what Microsoft Rep. had to say about the issue of “SORT button
implementing DISTINCT keyword also along with SORT functionalityâ€:
“...Just to let you know, we have submitted a bug years ago
(#R098292.OFFICE10 duplicated records are deleted after clicking sorting
button for a query), but for some backward compatibility reason, we can not
fix it. Since this issue exists in all version of Access, fix it will cause
some other problem. The bug has been shipped with all versions of access. In
Access 2000, someone fixed it for a period of time, then later submit found
that the fix broke the updateability of the query. We rolled back the fix and
kept the original behavior which was adding a distinct row flag when issue
the query. This is how it's working right now. The risk of changing this
behavior is very high. We have a lot of places when we go through this code
and in some places we do want the distinct flag to be set... â€

(I know Customers table should have one-to-many relationship with Orders
table not many-to-many; but that’s what I inherited to start work with…).
 
B

Brian

Afzal said:
I ran into this issue when query result would change when I clicked on the
Sort button for an INNER JOIN query (or any query) on two tables, CUSTOMERS
and ORDERDS, with Many-to-Many relationship. see "Wrong result from INNER
JOIN query if not ..." dated 3/30/2004.

This is what Microsoft Rep. had to say about the issue of "SORT button
implementing DISTINCT keyword also along with SORT functionality":
"...Just to let you know, we have submitted a bug years ago
(#R098292.OFFICE10 duplicated records are deleted after clicking sorting
button for a query), but for some backward compatibility reason, we can not
fix it. Since this issue exists in all version of Access, fix it will cause
some other problem. The bug has been shipped with all versions of access. In
Access 2000, someone fixed it for a period of time, then later submit found
that the fix broke the updateability of the query. We rolled back the fix and
kept the original behavior which was adding a distinct row flag when issue
the query. This is how it's working right now. The risk of changing this
behavior is very high. We have a lot of places when we go through this code
and in some places we do want the distinct flag to be set... "

(I know Customers table should have one-to-many relationship with Orders
table not many-to-many; but that's what I inherited to start work with.).
Click to expand...

Not something that's likely to trouble many of us, if it only occurs with
many-to-many relationships.
 
Top