Query sort not working after NULL column

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?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"mscertified" wrote:

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;

"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?

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.

"Jerry Whittle" wrote:

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?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"mscertified" wrote:

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;

"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?

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.

"Jerry Whittle" wrote:

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?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"mscertified" wrote:

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;

"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?

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" wrote in message
news
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?

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" wrote:

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" wrote in message
news
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?

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



"mscertified" wrote in message
...
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" wrote:

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" wrote in message
news
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?

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" wrote in message
...
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.

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" wrote:

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



"mscertified" wrote in message
...
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" wrote:

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" wrote in message
news 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?

You used the SQL statement:

SELECT Q.*, C.Desc AS ChapDesc, A.Desc AS ArtDesc FROM ...


and said the output was:



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


so, the last two columns on the output we 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

ORDER BY Q.Title, Q.Chapter, Q.Article, Q.Topic, Q.SubTopic, Q.PkgID;

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


"mscertified" wrote in message
...
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" wrote:

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



"mscertified" wrote in message
...
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" wrote:

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" wrote in message
news 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?