Query sort not working after NULL column

[mscertified]

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?

 

[Michel Walsh]

Probably because two nulls are not equal, so there is no need to "break the
equality" by looking further down (to column e) if two rows are equal by {a,
b, c}, and both null, under d.

You can try something like:


ORDER BY a, b, c, iif(d IS NULL, 32767 , d), e



assuming d is a numerical short integer, and that you wish consider NULLs in
the end of the sorted data.



Hoping it may help,
Vanderghast, Access MVP

 

[Jerry Whittle]

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.

 

[mscertified]

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

 

[mscertified]

I changed that column to be zeros instead of Nulls and it still sorts the
next column the same way (incorrectly). Now I'm even more confused.

 

[mscertified]

Just to throw a bit more light on this. This is a succession of 8 queries,
each of which reads in data from the queries before. Query 4 is a UNION query
and I was wondering if this could be a factor. Q.* contains the columns I
eventually sort on.

SELECT Q.*, DT.DateType, D.TargetDate, D.ActualDate
FROM qryRpt5YrProgress3 AS Q, tblPkgDates AS D, tblPkgDateTypes AS DT
WHERE Q.PkgID=D.PkgID And D.DateTypeID=DT.ID And (Q.PkgType = 'Exempt' AND
DT.DateType In ('NODO published','NODO published opt','Posted to
Internet','NOER published','Effective date')
OR Q.PkgType <> 'Exempt' AND DT.DateType In ('NODO published','NODO
published opt','NOPR published','NOPR published opt','NOFR published','NOFR
published opt','Effective date'))
UNION SELECT Q.*,Null,Null,Null
FROM qryRpt5YrProgress3 AS Q
WHERE Q.PkgID IS NULL;

 

[John Spencer]

Interesting. The data sorts correctly for me (Access 2003 SP2)

Is there a possibility that q.subTopic is not null, but is spaces or some
other not visible values.

What is in qryRpt5YrProgress7?

If you apply criteria to the query of
WHERE q.SubTopic is Null
so you still get the same records returned?

What happens if if you drop q.SubTopic from the ORDER BY clause? Do you
still see the same sorting problem?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[mscertified]

OK, I experimented, I placed the order by on the previous query and it sorted
correctly, so it's definitely being caused by the next query (which I
posted). Here is the previous query which sorts fine (when the order by is
added):

SELECT Q.*, D.TargetDate AS COADate
FROM qryRpt5YrProgress6 AS Q LEFT JOIN qryRuleDates AS D ON (D.RuleID =
Q.RuleID AND D.DateType = 'Course of Action');

It also appears to be nothing to do with the NULL values as I converted the
data from null to zero and it still mis-sorted.

 

[mscertified]

Some good ideas there.
I changed the data so the NULL column contains zero and the problem remains,
so the NULLs is not a factor. If I move the ORDER BY to qryRpt5YrProgress7,
that query sorts correctly, so the problem is related to this query not the
previous one.
I am using Access 2003 SP2
If I drop SubTopic from the ORDER BY, it sorts correctly!

 

[mscertified]

OK, I take it back. It is NOT working when I remove SubTopic from the sort. I
could have sworn I saw it work but I tried it a couple more times and it does
not work, it mis-sorts exactly as it did before. This is driving me nuts.

 

[Jerry Whittle]

Ouch! You've probably hit some sort (pardon the pun) of limitation with
Access.

Have you tried removing sorting from the other queries? Also making the
UNION a UNION all which will allow duplicates, but remove the need to sort in
that query?

 

[mscertified]

There is no sorting in any of the queries except the last one. I could remove
that and sort when the query is run from VB. I'll try changing the UNION to
UNION ALL but I'm sure that will complicate things even more and lead me to
have to insert even another extra query to get rid of the duplicates.

 

[mscertified]

I changed the UNION to UNION ALL in the earlier query, it actually made no
difference to the number of rows returned - however I still get the end
results sorted incorrectly. It made a difference in how they were ordered,
but they are still incorrect.

 

[Michel Walsh]

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]

That's the way the query is set up.
As far as I know there is no rule about matching the order of columns in the
'SELECT' clause with the order in the 'ORDER BY' clause. Correct me if I'm
wrong.

 

[Michel Walsh]

There is no such rule, indeed, BUT, to determine the order fails, you refer
to unmatched data, you refer to visual data obtained from references C and A
to CONCLUDE the ordering, on Q, is wrong, no?



Vanderghast, Access MVP

 

[Michel Walsh]

In fact, there is a rule: you ARE positively allowed to order on fields and
expression NOT selected.

It is just 'weird', sometimes, to order on data we don't "see".


Vanderghast, Access MVP

 

[mscertified]

I looked at the output of the query as displayed on my screen.
I'm not sure what you are getting at.

I now solved the problem by deleting the last query in the series (since the
previous query sorted) and adding the extra data in the report's format event.

So this will have to go down as an unsolved mystery. Its worrying though not
to be able to rely on the sorting mechanism.

 

[Michel Walsh]

You used the SQL statement:


and said the output was:




so, the last two columns on the output were: C.Desc (with the nulls) and
A.Desc (with 1867, 1852, 1852, 1867, ...)


But you did NOT order by on C.Desc, neither on A.Desc. You order on

BUT, it is NOT Q.SubTopic that supplies the NULL, neither Q.PkgID that
supplies 1867, 1852, .... that you show as OUTPUT. So, the ERROR is to
conclude ***from the OUTPUT*** that Q.Subtopic and Q.PkgID are not ORDER
BY correctly, since the output does not show these, but something else.



Vanderghast, Access MVP