Does a table join break Order By clause in Access Queries ?

Question

Hi all, I have a problem perplexing me to no end. When I run the following query against an access database:

SELECT *
FROM PreferredSpacer INNER JOIN SpacerThickness ON  PreferredSpacer.SpacerTypeID = SpacerThickness.SpacerTypeID
ORDER BY PreferredSpacer.UnitTypeID DESC

(UnitTypeID field is a text type)

The results do not come out sorted as a normal person would expect. They are all over the place with respect to the UnitTypeID field (There are entries starting with 'W' between entries starting with 'C' and 'M'). If I remove the join and just try to order the records in the PreferredSpacer table (which contains the UnitTypeID field) I get my expected results, so I must assume the join has something to do with it.

At the same time however, I honestly can't imagine a tool as ubiquitus as access could have such a glaring issue with a fairly basic query. If I am doing something wrong -- however -- I am not able to see what it could be.

Any assistance would be greatly appreciated. Thanks.

Answer 1

Does SpacerThickness have a UnitTypeID column? If so, the "*" in the select may mean that it's sorting on PreferredSpacer.UnitTypeID, but selecting SpacerThickness.UnitTypeID. Try selecting PreferredSpacer.UnitTypeID directly.

Answer 2

Unfortunately the UnitTypeID field only exists on the PreferredSpacer table.

Answer 3

I don't see anything that could be wrong with that query.

Is is possible that the joined table size is larger than access is willing to handle?

Answer 4

I tried rewriting it as you said: [EDIT: this was a response to a post that has been deleted, but the content is still valid]

SELECT PreferredSpacer.UnitTypeID
FROM PreferredSpacer, SpacerThickness
WHERE PreferredSpacer.SpacerTypeID = SpacerThickness.SpacerTypeID
ORDER BY PreferredSpacer.UnitTypeID DESC

And I still get the same results. Here is a C&P of some of the results if that might help anyone.

CPATA
CPATA
CFRSA
CFRSA
CFRSA
CFRSA
CFRSA
CFDOT
CFDOT
CFDOT
CFDOT
CFDOT
CFDOAVSL
CFDOAVSL
CFDOAVSL
CFDOAVSL
CFDOAVSL
CFDOA
CFDOA
CFDOA
CFDOA
CFDOA
CFDIAVSL
CFDIAVSL
CFDIAVSL
CFDIAVSL
CFDIAVSL
CFDIA
CFDIA
CFDIA
CFDIA
CFDIA
CFDAT
CFDAT
CFDAT
CFDAT
CFDAT
CBPATA
CBPATA
CBPATA
CBPATA
CBPATA
CBFRSA
CBFRSA
CBFRSA
CBFRSA
CBFRSA
CAPURE
CAPURE
CAPURE
CAPURE
CAPURE
CADGU
CADGU
CADGU
CADGU
CADGU
CADGS
CADGS
CADGS
CADGS
CADGS
COTR
COTR
COTR
COTR

As you can see, the results don't seem to follow any overall meaningful order.

Answer 5

Is that COTR or CzeroTR? Otherwise it looks sorted descending as requested. YesNo?

Answer 6

Google says:

You can not perform an ORDER BY on a text, ntext, or image field (those fields are actually pointers).

Its about MSSQL, but i imagine it's the same for Access.

Answer 7

To Remou: It's on O and not a zero, but even if it was there are lots more entries all over the place int he full result set (I just pasted a part of it here to illustrate, the whole thing is about 1,000 rows).

To dummy: Sql has the varchar type, which is what would be used in for this type of data there. Acccess however only has the text data type to cover both long and short strings. Also the order by does work If I am performing it only on the PreferredSpacer table (which is the one that contains the UnitTypeID field), it's only when I do the join that it falls apart.

Answer 8

It is certainly quite odd. Here is an article by Microsoft on sort order problems, perhaps it will help.

Answer 9

I figured it out. The tool our customer was using to generate the access DB in question was incorrectly turning varchar fields in SQL to memo fields in access (instead of text, as our tools do), and the memo field does not sort correctly. It seems odd to me that Access will just silently go along with it however, and not try to indicate that a sort on memo will not work as expected, but such is life.

Thanks for the responses everyone.