I've got a problem perplexing me to no finish. After I run the next query against an access database:

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

(UnitTypeID area is really a text type)

The outcomes are not equipped out sorted like a normal person would expect. They're everywhere regarding the UnitTypeID area (You will find records beginning with 'W' between records beginning with 'C' and 'M'). Basically take away the join and merely attempt to order the records within the PreferredSpacer table (which consists of the UnitTypeID area) I recieve my expected results, and so i must assume the join has something related to it.

Simultaneously however, I honestly can't make a tool as ubiquitus as access might have this type of glaring problem having a fairly fundamental query. Should i be doing a problem -- however -- I'm not capable of seeing what it may be.

Any assistance could be greatly appreciated. Thanks.

I believed it. The tool our customer was using to create the access DB under consideration was improperly turning varchar fields in SQL to memo fields in access (rather than text, as our tools do), and also the memo area doesn't sort properly. It appears odd in my experience that Access will just quietly accompany it however, and never attempt to indicate that the sort on memo won't act as expected, but such is existence.

Just reactions everybody.

Is the fact that COTR or CzeroTR? Otherwise it appears sorted climbing down as asked for. YesNo?

Does SpacerThickness possess a UnitTypeID column? If that's the case, the "*" within the choose may imply that it's sorting on PreferredSpacer.UnitTypeID, but choosing SpacerThickness.UnitTypeID. Try choosing PreferredSpacer.UnitTypeID directly.

Regrettably the UnitTypeID area only is available around the PreferredSpacer table.

I do not see anything that may be wrong with this query.

It's entirely possible that the became a member of table dimensions are bigger than access would like to deal with?

I attempted spinning it as being you stated: [EDIT: it was an answer to some publish that's been erased, however the submissions are still valid]

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

And That I get exactly the same results. This is a C&P of a few of the results if that can help anybody.

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 tell, the outcomes don't appear to follow along with any overall significant order.

To Remou: It's on O and never a zero, but even when it had been you will find much more records everywhere int he full result set (I simply copied and pasted part of it here as one example of, the entire factor is all about 1,000 rows).

To dummy: Sql has got the varchar type, that is an amount be utilized set for this kind of data there. Acccess however has only the written text data type to pay for both lengthy and short strings. Even the order by works Should i be carrying out it only around the PreferredSpacer table (the one which consists of the UnitTypeID area), it's only if I actually do the join it falls apart.

That is certainly quite odd. Here is an article by Microsoft on sort order problems, possibly it can help.

Google states:

You can't perform a purchase BY on the text, ntext, or image area (individuals fields are really pointers).

Its about MSSQL, however i imagine it is the same for Access.