Re: Query sort not working after NULL column

Tech-Archive recommends: Fix windows errors by optimizing your registry

You order on Q (Q.SubTopic, Q.PkgID) but the two last selected fields are from C and A (C.Desc, A.Desc). Is that a typo or a shortcut you used to illustrate the problem?

Vanderghast, Access MVP


"mscertified" <rupert@xxxxxxxxxxxxx> wrote in message news:D42FB41B-9C7B-4E02-A96C-A8E6DB79B569@xxxxxxxxxxxxxxxx
Query:

SELECT Q.*, C.Desc AS ChapDesc, A.Desc AS ArtDesc
FROM (qryRpt5YrProgress7 AS Q LEFT JOIN tblChapters AS C ON CInt(Q.Chapter)
= CInt(C.Chapter)) LEFT JOIN tblArticles AS A ON (Q.Title = A.Title) AND
(Q.Chapter = A.Chapter) AND (Q.Article = A.Article)
ORDER BY Q.Title, Q.Chapter, Q.Article, Q.Topic, Q.SubTopic, Q.PkgID;

Sample output:
R9,22,2,17,Null,1867
R9,22,2,17,Null,1852
R9,22,2,17,Null,1852
R9,22,2,17,Null,1867
....
R9,22,2,17,Null,1885
R9,22,2,17,Null,1852

As you can see, last column is unsorted



"Jerry Whittle" wrote:

Does the data in column E have numbers in it that look something like street
addressses: 123 Main St? Numbers in text strings can seem to be sorted
incorrectly, but actually are not.

Provide some sample data where the sort isn't working right.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"mscertified" wrote:

> I have a query that has an order by e.g.
> ORDER BY A,B,C,D,E
> For this particular subset of data, columns A, B and C are all the > same,
> column D is NULL in each row. However, column E has different values. > The
> sort is not sorting by column E. The only thing I can think of is the > NULLs
> in column D are throwing it off. Is this correct or should it sort > column E
> corectly even if column D is NULL.
>
> Hope someone can shed light on this?

.

Relevant Pages

  • Re: Query sort not working after NULL column
    ... There is no such rule, indeed, BUT, to determine the order fails, you refer ... Provide some sample data where the sort isn't working right. ... Jerry Whittle, Microsoft Access MVP ... Hope someone can shed light on this? ...
    (microsoft.public.access.queries)
  • Re: Query sort not working after NULL column
    ... 'SELECT' clause with the order in the 'ORDER BY' clause. ... Provide some sample data where the sort isn't working right. ... Hope someone can shed light on this? ...
    (microsoft.public.access.queries)
  • Re: Query sort not working after NULL column
    ... Sample output: ... Provide some sample data where the sort isn't working right. ... Jerry Whittle, Microsoft Access MVP ... Hope someone can shed light on this? ...
    (microsoft.public.access.queries)
  • Re: Query sort not working after NULL column
    ... I looked at the output of the query as displayed on my screen. ... Provide some sample data where the sort isn't working right. ... Jerry Whittle, Microsoft Access MVP ... Hope someone can shed light on this? ...
    (microsoft.public.access.queries)
  • Re: Exracting numerical data.
    ... Based on your sample data - if it is all in one column (but with rows ... irregularly between them) - then I would select the whole column and "Sort". ... I know there are available add-in user functions based on a loop ... The irregular spacing is for real. ...
    (microsoft.public.excel)