If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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? |
#12
|
|||
|
|||
Query sort not working after NULL column
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? |
#13
|
|||
|
|||
Query sort not working after NULL column
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? |
#14
|
|||
|
|||
Query sort not working after NULL column
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? |
#15
|
|||
|
|||
Query sort not working after NULL column
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? |
#16
|
|||
|
|||
Query sort not working after NULL column
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? |
#17
|
|||
|
|||
Query sort not working after NULL column
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. |
#18
|
|||
|
|||
Query sort not working after NULL column
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? |
#19
|
|||
|
|||
Query sort not working after NULL column
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? |
|
Thread Tools | |
Display Modes | |
|
|