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 |
#21
|
|||
|
|||
Query doesn't always sort
You can try the following if your table structure is always consistent.
-- Define an table structure - TheTableBlank - with all your fields and add a sequential autonumber field as the primary key -- Copy the table structure (Docmd.CopyObject Currentdb().Name,"TheTable",acTable,"TheTableBlank ") -- APPEND your records to the table you have just created THAT MAY give you the records in sequence. BUT remember TABLES are unordered. If you want to impose an order you must sort the data either using a query or in Access imposing a sort order on the datasheet view. If you don't impose an order on the datasheet view of a table and there is a primary key in the table, then Access will show the records in primary key order (at least that is the behavior I have observed) -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. wrote in message ups.com... John, Do you mean that I would have to create a blank table in a query and then append the data to it, as per Gary's suggestion? Or do you have another workaround? Thanks, Sam On May 1, 2:47 pm, "John Spencer" wrote: I just realized that you are using the query to do a make table. ============ Speculation follows =============== Make table queries can more or less ignore the sort order when they are creating the table. Even if the records were written in the sorted order, they may or may not be written to the disk in any specific order as far as disk storage order goes. Sowhen they are retrieved they aren't retrieved in the write order but in disk access order (if no specific order is available). If you have a primary key then Access will use that as the default order when it displays the table in DataSheet view. =========== End of speculation ================= BUT tables have no inherent order. If you want to impose a reliable order you must sort the data in a query based on the table you have created. Sorry, but that is the way it is (at least as far as I know). -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . wrote in message oups.com... John, From my experience and that of my collegues with a similar problem, this sometimes occurs even after a compact and repair. Also, it seems to be happening with multiple databases on a regular basis, so I don't see this as a corrupt database issue. Any other ideas? Thanks, Sam On Apr 27, 2:22 pm, "John Spencer" wrote: A couple of things to try. Try using DISTINCTROW in the query. That may take care of the problem. It is possible that an index has gotten corrupted. Try removing all indexes on the table, doing a compact and repair, and then restoring the indexes. Perhaps you have a corrupted index. SELECT DISTINCTROW P.GEOSORT, P.TERR_CODE_IND AS TERR_CODE, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_SHORT_NAME, P.M23VA, P.M22VA, P.M21VA, P.M20VA, P.M19VA, P.M18VA, P.M17VA, P.M16VA, P.M15VA, P.M14VA, P.M13VA, P.M12VA, P.M11VA, P.M10VA, P.M9VA, P.M8VA, P.M7VA, P.M6VA, P.M5VA, P.M4VA, P.M3VA, P.M2VA, P.M1VA, P.CMTHVA, P.M23VC, P.M22VC, P.M21VC, P.M20VC, P.M19VC, P.M18VC, P.M17VC, P.M16VC, P.M15VC, P.M14VC, P.M13VC, P.M12VC, P.M11VC, P.M10VC, P.M9VC, P.M8VC, P.M7VC, P.M6VC, P.M5VC, P.M4VC, P.M3VC, P.M2VC, P.M1VC, P.CMTHVC, P.M23VSAA, P.M22VSAA, P.M21VSAA, P.M20VSAA, P.M19VSAA, P.M18VSAA, P.M17VSAA, P.M16VSAA, P.M15VSAA, P.M14VSAA, P.M13VSAA, P.M12VSAA, P.M11VSAA, P.M10VSAA, P.M9VSAA, P.M8VSAA, P.M7VSAA, P.M6VSAA, P.M5VSAA, P.M4VSAA, P.M3VSAA, P.M2VSAA, P.M1VSAA, P.CMTHVSAA, P.M23VSAC, P.M22VSAC, P.M21VSAC, P.M20VSAC, P.M19VSAC, P.M18VSAC, P.M17VSAC, P.M16VSAC, P.M15VSAC, P.M14VSAC, P.M13VSAC, P.M12VSAC, P.M11VSAC, P.M10VSAC, P.M9VSAC, P.M8VSAC, P.M7VSAC, P.M6VSAC, P.M5VSAC, P.M4VSAC, P.M3VSAC, P.M2VSAC, P.M1VSAC, P.CMTHVSAC INTO SORTED_P FROM P ORDER BY P.GEOSORT, P.TERR_CODE_IND, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_LOGICAL_ORDER; -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . wrote in message roups.com... John, thanks for your response. It's not sorted because, in the datasheet view of the table, some lines are clearly out of place and not sorted. I need to deliver these tables directly to my client, but sorted in a particular way. The sort works most of the time, but it sometimes leaves a big chunk of the table unsorted. When I see it, I usually rerun the query once or twice and it works. The problem is that I can't automate the entire procedure through a macro because of this. Any advice would be appreciated. On Apr 27, 11:55 am, "John Spencer" wrote: Why do you say it doesn't always sort? Do you see the behavior when you use the query as the source for a report? If so, then you need to be aware that reports more or less ignore any sorting done by the query. You need to use the report's Sorting and Group (View: Sorting and Grouping) to set up the sorting. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . wrote in message groups.com... Hello, I have an access query that I run on a regular basis but that doesn't always sort. It sorts most of the time, but once in a while it won't, which makes it really annoying and impractical. Does anybody know why this is happening? Thank you. The query is: SELECT P.GEOSORT, P.TERR_CODE_IND AS TERR_CODE, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_SHORT_NAME, P.M23VA, P.M22VA, P.M21VA, P.M20VA, P.M19VA, P.M18VA, P.M17VA, P.M16VA, P.M15VA, P.M14VA, P.M13VA, P.M12VA, P.M11VA, P.M10VA, P.M9VA, P.M8VA, P.M7VA, P.M6VA, P.M5VA, P.M4VA, P.M3VA, P.M2VA, P.M1VA, P.CMTHVA, P.M23VC, P.M22VC, P.M21VC, P.M20VC, P.M19VC, P.M18VC, P.M17VC, P.M16VC, P.M15VC, P.M14VC, P.M13VC, P.M12VC, P.M11VC, P.M10VC, P.M9VC, P.M8VC, P.M7VC, P.M6VC, P.M5VC, P.M4VC, P.M3VC, P.M2VC, P.M1VC, P.CMTHVC, P.M23VSAA, P.M22VSAA, P.M21VSAA, P.M20VSAA, P.M19VSAA, P.M18VSAA, P.M17VSAA, P.M16VSAA, P.M15VSAA, P.M14VSAA, P.M13VSAA, P.M12VSAA, P.M11VSAA, P.M10VSAA, P.M9VSAA, P.M8VSAA, P.M7VSAA, P.M6VSAA, P.M5VSAA, P.M4VSAA, P.M3VSAA, P.M2VSAA, P.M1VSAA, P.CMTHVSAA, P.M23VSAC, P.M22VSAC, P.M21VSAC, P.M20VSAC, P.M19VSAC, P.M18VSAC, P.M17VSAC, P.M16VSAC, P.M15VSAC, P.M14VSAC, P.M13VSAC, P.M12VSAC, P.M11VSAC, P.M10VSAC, P.M9VSAC, P.M8VSAC, P.M7VSAC, P.M6VSAC, P.M5VSAC, P.M4VSAC, P.M3VSAC, P.M2VSAC, P.M1VSAC, P.CMTHVSAC INTO SORTED_P FROM P ORDER BY P.GEOSORT, P.TERR_CODE_IND, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_LOGICAL_ORDER;- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#22
|
|||
|
|||
Query doesn't always sort
Hi Sam,
If the table already exists then there is no reason to make it again. Create a query that will clear that table (for example if the table name was "Table1"): qryClearTable1 DELETE * FROM Table1; then you could in a macro run qryClearTable1 then the append query you saved... sam.alame wrote: Thank you very much Gary; I will ask my colleague to give this a try with her database today. Since my ultimate aim is to run the entire process through a macro, do you think it would work equally well if I created two queries, one to create a blank table and the other to append? (I don't see why it shouldn't but, given these limitations I didn't know about, I thought it would be wise to ask!) Thanks again, Sam On May 2, 6:44 am, "Gary Walter" wrote: Hi Sam, Okay.... It sounds like you have this need to perform this task only in the GUI... One workaround: Set up your make-table query w/sorting. Add one more column Field: 1 Table: Sort: Show: unchecked Criteria: 0 Or: Run your make-table query. (this will make the table, but not add data) Change your query to an append query. Change that column (or just delete it) Field: 1 Table: Sort: Show: unchecked Criteria: 1 Or: Run the append query. sam.alame wrote: To tell you the truth, this SQL code was not tweaked manually. It's a direct result of creating a make-table query in the access GUI. Should I tweak it as per your suggestion? How would that reflect on my ability to change the query using the Access GUI in the future? As for the make-table query, it is necessary because: a) the data must be sorted and b) more than one final table is produced from the same source tables, depending on which fields are selected. Your input would be appreciated. Thanks, Sam On Apr 29, 7:04 pm, "Gary Walter" wrote: Hi Sam, Pardon me for jumping in... I wonder if JensB is maybe thinking of SQL Server? I don't believe the query is from SQL2K since it does not start with "TOP 100%" which is necessary for ORDER BY in SQL2K (I don't work w/ newer version of SQL Server so don't know if that has changed). With Jet queries, you cannot use alias in ORDER BY clause. ORDER BY tangents: -- In a UNION query, you can set an alias in the first SELECT, then at the end of all SELECTs, you do order by that alias. -- Your ORDER BY can use the ordinal position of the field in the SELECT query. SELECT f1, f2 FROM tbl ORDER BY 2; would sort by f2. I defer to both John's posts (they know much more than I), but wonder about 2 things: 1) Why is P.PRODUCT_LOGICAL_ORDER in the ORDER BY but not in the SELECT clause? Would it hurt to put it in the SELECT clause and test for consistency? Or just leave it out of ORDER BY and test? 2) Why use a make table? Have you tried emptying the table in code, then appending new data, where your append query also includes field mentioned above in SELECT clause (so also in empty table) ? Good luck, gary "JensB" wrote Yes The original fieldname does not exist in your result. JensB wrote in message oups.com... JensB, thanks for your response. So all I need to do is order by the alias as opposed to the original field name? On Apr 27, 10:37 am, "JensB" wrote: Try to use the alias for P.TERR_CODE_IND like this: ORDER BY P.GEOSORT, TERR_CODE, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_LOGICAL_ORDER wrote in message egroups.com... Hello I have an access query that I run on a regular basis but that doesn't always sort. It sorts most of the time, but once in a while it won't, which makes it really annoying and impractical. Does anybody know why this is happening? Thank you. The query is: SELECT P.GEOSORT, P.TERR_CODE_IND AS TERR_CODE, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_SHORT_NAME, P.M23VA, P.M22VA, P.M21VA, P.M20VA, P.M19VA, P.M18VA, P.M17VA, P.M16VA, P.M15VA, P.M14VA, P.M13VA, P.M12VA, P.M11VA, P.M10VA, P.M9VA, P.M8VA, P.M7VA, P.M6VA, P.M5VA, P.M4VA, P.M3VA, P.M2VA, P.M1VA, P.CMTHVA, P.M23VC, P.M22VC, P.M21VC, P.M20VC, P.M19VC, P.M18VC, P.M17VC, P.M16VC, P.M15VC, P.M14VC, P.M13VC, P.M12VC, P.M11VC, P.M10VC, P.M9VC, P.M8VC, P.M7VC, P.M6VC, P.M5VC, P.M4VC, P.M3VC, P.M2VC, P.M1VC, P.CMTHVC, P.M23VSAA, P.M22VSAA, P.M21VSAA, P.M20VSAA, P.M19VSAA, P.M18VSAA, P.M17VSAA, P.M16VSAA, P.M15VSAA, P.M14VSAA, P.M13VSAA, P.M12VSAA, P.M11VSAA, P.M10VSAA, P.M9VSAA, P.M8VSAA, P.M7VSAA, P.M6VSAA, P.M5VSAA, P.M4VSAA, P.M3VSAA, P.M2VSAA, P.M1VSAA, P.CMTHVSAA, P.M23VSAC, P.M22VSAC, P.M21VSAC, P.M20VSAC, P.M19VSAC, P.M18VSAC, P.M17VSAC, P.M16VSAC, P.M15VSAC, P.M14VSAC, P.M13VSAC, P.M12VSAC, P.M11VSAC, P.M10VSAC, P.M9VSAC, P.M8VSAC, P.M7VSAC, P.M6VSAC, P.M5VSAC, P.M4VSAC, P.M3VSAC, P.M2VSAC, P.M1VSAC, P.CMTHVSAC INTO SORTED_P FROM P ORDER BY P.GEOSORT, P.TERR_CODE_IND, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_LOGICAL_ORDER;- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#23
|
|||
|
|||
Query doesn't always sort
Hi Gary,
Your solution seemed to have significantly reduced the number of unsorted tables produced, but I'm still getting a couple of unsorted tables here and there (about 2 out of 30). Is there anything else that you could suggest? FYI, there is a field in the original table that I am sorting by but not bringing into the new table. Could this be causing some of the issues? Thanks again, your help is much appreciated, Sam On May 2, 9:51 am, "Gary Walter" wrote: Hi Sam, If the table already exists then there is no reason to make it again. Create a query that will clear that table (for example if the table name was "Table1"): qryClearTable1 DELETE * FROM Table1; then you could in a macro run qryClearTable1 then the append query you saved... sam.alame wrote: Thank you very much Gary; I will ask my colleague to give this a try with her database today. Since my ultimate aim is to run the entire process through a macro, do you think it would work equally well if I created two queries, one to create a blank table and the other to append? (I don't see why it shouldn't but, given these limitations I didn't know about, I thought it would be wise to ask!) Thanks again, Sam On May 2, 6:44 am, "Gary Walter" wrote: Hi Sam, Okay.... It sounds like you have this need to perform this task only in the GUI... One workaround: Set up your make-table query w/sorting. Add one more column Field: 1 Table: Sort: Show: unchecked Criteria: 0 Or: Run your make-table query. (this will make the table, but not add data) Change your query to an append query. Change that column (or just delete it) Field: 1 Table: Sort: Show: unchecked Criteria: 1 Or: Run the append query. sam.alame wrote: To tell you the truth, this SQL code was not tweaked manually. It's a direct result of creating a make-table query in the access GUI. Should I tweak it as per your suggestion? How would that reflect on my ability to change the query using the Access GUI in the future? As for the make-table query, it is necessary because: a) the data must be sorted and b) more than one final table is produced from the same source tables, depending on which fields are selected. Your input would be appreciated. Thanks, Sam On Apr 29, 7:04 pm, "Gary Walter" wrote: Hi Sam, Pardon me for jumping in... I wonder if JensB is maybe thinking of SQL Server? I don't believe the query is from SQL2K since it does not start with "TOP 100%" which is necessary for ORDER BY in SQL2K (I don't work w/ newer version of SQL Server so don't know if that has changed). With Jet queries, you cannot use alias in ORDER BY clause. ORDER BY tangents: -- In a UNION query, you can set an alias in the first SELECT, then at the end of all SELECTs, you do order by that alias. -- Your ORDER BY can use the ordinal position of the field in the SELECT query. SELECT f1, f2 FROM tbl ORDER BY 2; would sort by f2. I defer to both John's posts (they know much more than I), but wonder about 2 things: 1) Why is P.PRODUCT_LOGICAL_ORDER in the ORDER BY but not in the SELECT clause? Would it hurt to put it in the SELECT clause and test for consistency? Or just leave it out of ORDER BY and test? 2) Why use a make table? Have you tried emptying the table in code, then appending new data, where your append query also includes field mentioned above in SELECT clause (so also in empty table) ? Good luck, gary "JensB" wrote Yes The original fieldname does not exist in your result. JensB wrote in message oups.com... JensB, thanks for your response. So all I need to do is order by the alias as opposed to the original field name? On Apr 27, 10:37 am, "JensB" wrote: Try to use the alias for P.TERR_CODE_IND like this: ORDER BY P.GEOSORT, TERR_CODE, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_LOGICAL_ORDER wrote in message egroups.com... Hello I have an access query that I run on a regular basis but that doesn't always sort. It sorts most of the time, but once in a while it won't, which makes it really annoying and impractical. Does anybody know why this is happening? Thank you. The query is: SELECT P.GEOSORT, P.TERR_CODE_IND AS TERR_CODE, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_SHORT_NAME, P.M23VA, P.M22VA, P.M21VA, P.M20VA, P.M19VA, P.M18VA, P.M17VA, P.M16VA, P.M15VA, P.M14VA, P.M13VA, P.M12VA, P.M11VA, P.M10VA, P.M9VA, P.M8VA, P.M7VA, P.M6VA, P.M5VA, P.M4VA, P.M3VA, P.M2VA, P.M1VA, P.CMTHVA, P.M23VC, P.M22VC, P.M21VC, P.M20VC, P.M19VC, P.M18VC, P.M17VC, P.M16VC, P.M15VC, P.M14VC, P.M13VC, P.M12VC, P.M11VC, P.M10VC, P.M9VC, P.M8VC, P.M7VC, P.M6VC, P.M5VC, P.M4VC, P.M3VC, P.M2VC, P.M1VC, P.CMTHVC, P.M23VSAA, P.M22VSAA, P.M21VSAA, P.M20VSAA, P.M19VSAA, P.M18VSAA, P.M17VSAA, P.M16VSAA, P.M15VSAA, P.M14VSAA, P.M13VSAA, P.M12VSAA, P.M11VSAA, P.M10VSAA, P.M9VSAA, P.M8VSAA, P.M7VSAA, P.M6VSAA, P.M5VSAA, P.M4VSAA, P.M3VSAA, P.M2VSAA, P.M1VSAA, P.CMTHVSAA, P.M23VSAC, P.M22VSAC, P.M21VSAC, P.M20VSAC, P.M19VSAC, P.M18VSAC, P.M17VSAC, P.M16VSAC, P.M15VSAC, P.M14VSAC, P.M13VSAC, P.M12VSAC, P.M11VSAC, P.M10VSAC, P.M9VSAC, P.M8VSAC, P.M7VSAC, P.M6VSAC, P.M5VSAC, P.M4VSAC, P.M3VSAC, P.M2VSAC, P.M1VSAC, P.CMTHVSAC INTO SORTED_P FROM P ORDER BY P.GEOSORT, P.TERR_CODE_IND, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_LOGICAL_ORDER;- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#24
|
|||
|
|||
Query doesn't always sort
Hi John,
My table doesn't have a primary key. The 'unsort' seems to be completely random. Also, my table structures are not very consistent, as I'm dealing with quite a few tables in multiple databases. How do I impose a sort on the datasheet view? Would that actually order the table? I am asking because after the final tables are produced, I have a tool that splits and exports the data into Excel, and I need the data to be sorted over there as well. Thanks, Sam On May 2, 9:05 am, "John Spencer" wrote: You can try the following if your table structure is always consistent. -- Define an table structure - TheTableBlank - with all your fields and add a sequential autonumber field as the primary key -- Copy the table structure (Docmd.CopyObject Currentdb().Name,"TheTable",acTable,"TheTableBlank ") -- APPEND your records to the table you have just created THAT MAY give you the records in sequence. BUT remember TABLES are unordered. If you want to impose an order you must sort the data either using a query or in Access imposing a sort order on the datasheet view. If you don't impose an order on the datasheet view of a table and there is a primary key in the table, then Access will show the records in primary key order (at least that is the behavior I have observed) -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . wrote in message ups.com... John, Do you mean that I would have to create a blank table in a query and then append the data to it, as per Gary's suggestion? Or do you have another workaround? Thanks, Sam On May 1, 2:47 pm, "John Spencer" wrote: I just realized that you are using the query to do a make table. ============ Speculation follows =============== Make table queries can more or less ignore the sort order when they are creating the table. Even if the records were written in the sorted order, they may or may not be written to the disk in any specific order as far as disk storage order goes. Sowhen they are retrieved they aren't retrieved in the write order but in disk access order (if no specific order is available). If you have a primary key then Access will use that as the default order when it displays the table in DataSheet view. =========== End of speculation ================= BUT tables have no inherent order. If you want to impose a reliable order you must sort the data in a query based on the table you have created. Sorry, but that is the way it is (at least as far as I know). -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . wrote in message groups.com... John, From my experience and that of my collegues with a similar problem, this sometimes occurs even after a compact and repair. Also, it seems to be happening with multiple databases on a regular basis, so I don't see this as a corrupt database issue. Any other ideas? Thanks, Sam On Apr 27, 2:22 pm, "John Spencer" wrote: A couple of things to try. Try using DISTINCTROW in the query. That may take care of the problem. It is possible that an index has gotten corrupted. Try removing all indexes on the table, doing a compact and repair, and then restoring the indexes. Perhaps you have a corrupted index. SELECT DISTINCTROW P.GEOSORT, P.TERR_CODE_IND AS TERR_CODE, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_SHORT_NAME, P.M23VA, P.M22VA, P.M21VA, P.M20VA, P.M19VA, P.M18VA, P.M17VA, P.M16VA, P.M15VA, P.M14VA, P.M13VA, P.M12VA, P.M11VA, P.M10VA, P.M9VA, P.M8VA, P.M7VA, P.M6VA, P.M5VA, P.M4VA, P.M3VA, P.M2VA, P.M1VA, P.CMTHVA, P.M23VC, P.M22VC, P.M21VC, P.M20VC, P.M19VC, P.M18VC, P.M17VC, P.M16VC, P.M15VC, P.M14VC, P.M13VC, P.M12VC, P.M11VC, P.M10VC, P.M9VC, P.M8VC, P.M7VC, P.M6VC, P.M5VC, P.M4VC, P.M3VC, P.M2VC, P.M1VC, P.CMTHVC, P.M23VSAA, P.M22VSAA, P.M21VSAA, P.M20VSAA, P.M19VSAA, P.M18VSAA, P.M17VSAA, P.M16VSAA, P.M15VSAA, P.M14VSAA, P.M13VSAA, P.M12VSAA, P.M11VSAA, P.M10VSAA, P.M9VSAA, P.M8VSAA, P.M7VSAA, P.M6VSAA, P.M5VSAA, P.M4VSAA, P.M3VSAA, P.M2VSAA, P.M1VSAA, P.CMTHVSAA, P.M23VSAC, P.M22VSAC, P.M21VSAC, P.M20VSAC, P.M19VSAC, P.M18VSAC, P.M17VSAC, P.M16VSAC, P.M15VSAC, P.M14VSAC, P.M13VSAC, P.M12VSAC, P.M11VSAC, P.M10VSAC, P.M9VSAC, P.M8VSAC, P.M7VSAC, P.M6VSAC, P.M5VSAC, P.M4VSAC, P.M3VSAC, P.M2VSAC, P.M1VSAC, P.CMTHVSAC INTO SORTED_P FROM P ORDER BY P.GEOSORT, P.TERR_CODE_IND, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_LOGICAL_ORDER; -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . wrote in message roups.com... John, thanks for your response. It's not sorted because, in the datasheet view of the table, some lines are clearly out of place and not sorted. I need to deliver these tables directly to my client, but sorted in a particular way. The sort works most of the time, but it sometimes leaves a big chunk of the table unsorted. When I see it, I usually rerun the query once or twice and it works. The problem is that I can't automate the entire procedure through a macro because of this. Any advice would be appreciated. On Apr 27, 11:55 am, "John Spencer" wrote: Why do you say it doesn't always sort? Do you see the behavior when you use the query as the source for a report? If so, then you need to be aware that reports more or less ignore any sorting done by the query. You need to use the report's Sorting and Group (View: Sorting and Grouping) to set up the sorting. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . wrote in message groups.com... Hello, I have an access query that I run on a regular basis but that doesn't always sort. It sorts most of the time, but once in a while it won't, which makes it really annoying and impractical. Does anybody know why this is happening? Thank you. The query is: SELECT P.GEOSORT, P.TERR_CODE_IND AS TERR_CODE, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_SHORT_NAME, P.M23VA, P.M22VA, P.M21VA, P.M20VA, P.M19VA, P.M18VA, P.M17VA, P.M16VA, P.M15VA, P.M14VA, P.M13VA, P.M12VA, P.M11VA, P.M10VA, P.M9VA, P.M8VA, P.M7VA, P.M6VA, P.M5VA, P.M4VA, P.M3VA, P.M2VA, P.M1VA, P.CMTHVA, P.M23VC, P.M22VC, P.M21VC, P.M20VC, P.M19VC, P.M18VC, P.M17VC, P.M16VC, P.M15VC, P.M14VC, P.M13VC, P.M12VC, P.M11VC, P.M10VC, P.M9VC, P.M8VC, P.M7VC, P.M6VC, P.M5VC, P.M4VC, P.M3VC, P.M2VC, P.M1VC, P.CMTHVC, P.M23VSAA, P.M22VSAA, P.M21VSAA, P.M20VSAA, P.M19VSAA, P.M18VSAA, P.M17VSAA, P.M16VSAA, P.M15VSAA, P.M14VSAA, P.M13VSAA, P.M12VSAA, P.M11VSAA, P.M10VSAA, P.M9VSAA, P.M8VSAA, P.M7VSAA, P.M6VSAA, P.M5VSAA, P.M4VSAA, P.M3VSAA, P.M2VSAA, P.M1VSAA, P.CMTHVSAA, P.M23VSAC, P.M22VSAC, P.M21VSAC, P.M20VSAC, P.M19VSAC, P.M18VSAC, P.M17VSAC, P.M16VSAC, P.M15VSAC, P.M14VSAC, P.M13VSAC, P.M12VSAC, P.M11VSAC, P.M10VSAC, P.M9VSAC, P.M8VSAC, P.M7VSAC, P.M6VSAC, P.M5VSAC, P.M4VSAC, P.M3VSAC, P.M2VSAC, P.M1VSAC, P.CMTHVSAC INTO SORTED_P FROM P ORDER BY P.GEOSORT, P.TERR_CODE_IND, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_LOGICAL_ORDER;- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#25
|
|||
|
|||
Query doesn't always sort
sam.alame wrote: Your solution seemed to have significantly reduced the number of unsorted tables produced, but I'm still getting a couple of unsorted tables here and there (about 2 out of 30). Is there anything else that you could suggest? FYI, there is a field in the original table that I am sorting by but not bringing into the new table. Could this be causing some of the issues? Quite honestly Sam I don't know (that's why I asked the question in my first post). Does append only produce unsorted when you don't include a sort-by field into the table, i.e., did you test it by including it in the table? I said earlier that I defer to the Johns' posts. A table is just a "repository" of data (a "bag of marbles" as John V once said). It is not an Excel spreadsheet. I personally never expect a table to be sorted. My users never see a table, only a form or report that has been sorted via a query (form or control on a form) or the report's builtin sorting settings. I do send a lot of properly sorted data to Excel (and some to xml) that is all sorted via a query. Tables hold data, queries sort data. Good luck, gary |
#26
|
|||
|
|||
Query doesn't always sort
I'm sorry, but nothing you can do will guarantee that the table is sorted in
any special order. You have to use a query to sort the data in to your required order and then export the query. IF you aren't bringing in the fields that you need to determine the order you need and then using them in a query to export the data, then as far as I know there is no reliable solution. As I've repeatedly said, records in tables have no order. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. wrote in message ps.com... Hi John, My table doesn't have a primary key. The 'unsort' seems to be completely random. Also, my table structures are not very consistent, as I'm dealing with quite a few tables in multiple databases. How do I impose a sort on the datasheet view? Would that actually order the table? I am asking because after the final tables are produced, I have a tool that splits and exports the data into Excel, and I need the data to be sorted over there as well. Thanks, Sam On May 2, 9:05 am, "John Spencer" wrote: You can try the following if your table structure is always consistent. -- Define an table structure - TheTableBlank - with all your fields and add a sequential autonumber field as the primary key -- Copy the table structure (Docmd.CopyObject Currentdb().Name,"TheTable",acTable,"TheTableBlank ") -- APPEND your records to the table you have just created THAT MAY give you the records in sequence. BUT remember TABLES are unordered. If you want to impose an order you must sort the data either using a query or in Access imposing a sort order on the datasheet view. If you don't impose an order on the datasheet view of a table and there is a primary key in the table, then Access will show the records in primary key order (at least that is the behavior I have observed) -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . wrote in message ups.com... John, Do you mean that I would have to create a blank table in a query and then append the data to it, as per Gary's suggestion? Or do you have another workaround? Thanks, Sam On May 1, 2:47 pm, "John Spencer" wrote: I just realized that you are using the query to do a make table. ============ Speculation follows =============== Make table queries can more or less ignore the sort order when they are creating the table. Even if the records were written in the sorted order, they may or may not be written to the disk in any specific order as far as disk storage order goes. Sowhen they are retrieved they aren't retrieved in the write order but in disk access order (if no specific order is available). If you have a primary key then Access will use that as the default order when it displays the table in DataSheet view. =========== End of speculation ================= BUT tables have no inherent order. If you want to impose a reliable order you must sort the data in a query based on the table you have created. Sorry, but that is the way it is (at least as far as I know). -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . wrote in message groups.com... John, From my experience and that of my collegues with a similar problem, this sometimes occurs even after a compact and repair. Also, it seems to be happening with multiple databases on a regular basis, so I don't see this as a corrupt database issue. Any other ideas? Thanks, Sam On Apr 27, 2:22 pm, "John Spencer" wrote: A couple of things to try. Try using DISTINCTROW in the query. That may take care of the problem. It is possible that an index has gotten corrupted. Try removing all indexes on the table, doing a compact and repair, and then restoring the indexes. Perhaps you have a corrupted index. SELECT DISTINCTROW P.GEOSORT, P.TERR_CODE_IND AS TERR_CODE, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_SHORT_NAME, P.M23VA, P.M22VA, P.M21VA, P.M20VA, P.M19VA, P.M18VA, P.M17VA, P.M16VA, P.M15VA, P.M14VA, P.M13VA, P.M12VA, P.M11VA, P.M10VA, P.M9VA, P.M8VA, P.M7VA, P.M6VA, P.M5VA, P.M4VA, P.M3VA, P.M2VA, P.M1VA, P.CMTHVA, P.M23VC, P.M22VC, P.M21VC, P.M20VC, P.M19VC, P.M18VC, P.M17VC, P.M16VC, P.M15VC, P.M14VC, P.M13VC, P.M12VC, P.M11VC, P.M10VC, P.M9VC, P.M8VC, P.M7VC, P.M6VC, P.M5VC, P.M4VC, P.M3VC, P.M2VC, P.M1VC, P.CMTHVC, P.M23VSAA, P.M22VSAA, P.M21VSAA, P.M20VSAA, P.M19VSAA, P.M18VSAA, P.M17VSAA, P.M16VSAA, P.M15VSAA, P.M14VSAA, P.M13VSAA, P.M12VSAA, P.M11VSAA, P.M10VSAA, P.M9VSAA, P.M8VSAA, P.M7VSAA, P.M6VSAA, P.M5VSAA, P.M4VSAA, P.M3VSAA, P.M2VSAA, P.M1VSAA, P.CMTHVSAA, P.M23VSAC, P.M22VSAC, P.M21VSAC, P.M20VSAC, P.M19VSAC, P.M18VSAC, P.M17VSAC, P.M16VSAC, P.M15VSAC, P.M14VSAC, P.M13VSAC, P.M12VSAC, P.M11VSAC, P.M10VSAC, P.M9VSAC, P.M8VSAC, P.M7VSAC, P.M6VSAC, P.M5VSAC, P.M4VSAC, P.M3VSAC, P.M2VSAC, P.M1VSAC, P.CMTHVSAC INTO SORTED_P FROM P ORDER BY P.GEOSORT, P.TERR_CODE_IND, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_LOGICAL_ORDER; -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . wrote in message roups.com... John, thanks for your response. It's not sorted because, in the datasheet view of the table, some lines are clearly out of place and not sorted. I need to deliver these tables directly to my client, but sorted in a particular way. The sort works most of the time, but it sometimes leaves a big chunk of the table unsorted. When I see it, I usually rerun the query once or twice and it works. The problem is that I can't automate the entire procedure through a macro because of this. Any advice would be appreciated. On Apr 27, 11:55 am, "John Spencer" wrote: Why do you say it doesn't always sort? Do you see the behavior when you use the query as the source for a report? If so, then you need to be aware that reports more or less ignore any sorting done by the query. You need to use the report's Sorting and Group (View: Sorting and Grouping) to set up the sorting. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . wrote in message groups.com... Hello, I have an access query that I run on a regular basis but that doesn't always sort. It sorts most of the time, but once in a while it won't, which makes it really annoying and impractical. Does anybody know why this is happening? Thank you. The query is: SELECT P.GEOSORT, P.TERR_CODE_IND AS TERR_CODE, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_SHORT_NAME, P.M23VA, P.M22VA, P.M21VA, P.M20VA, P.M19VA, P.M18VA, P.M17VA, P.M16VA, P.M15VA, P.M14VA, P.M13VA, P.M12VA, P.M11VA, P.M10VA, P.M9VA, P.M8VA, P.M7VA, P.M6VA, P.M5VA, P.M4VA, P.M3VA, P.M2VA, P.M1VA, P.CMTHVA, P.M23VC, P.M22VC, P.M21VC, P.M20VC, P.M19VC, P.M18VC, P.M17VC, P.M16VC, P.M15VC, P.M14VC, P.M13VC, P.M12VC, P.M11VC, P.M10VC, P.M9VC, P.M8VC, P.M7VC, P.M6VC, P.M5VC, P.M4VC, P.M3VC, P.M2VC, P.M1VC, P.CMTHVC, P.M23VSAA, P.M22VSAA, P.M21VSAA, P.M20VSAA, P.M19VSAA, P.M18VSAA, P.M17VSAA, P.M16VSAA, P.M15VSAA, P.M14VSAA, P.M13VSAA, P.M12VSAA, P.M11VSAA, P.M10VSAA, P.M9VSAA, P.M8VSAA, P.M7VSAA, P.M6VSAA, P.M5VSAA, P.M4VSAA, P.M3VSAA, P.M2VSAA, P.M1VSAA, P.CMTHVSAA, P.M23VSAC, P.M22VSAC, P.M21VSAC, P.M20VSAC, P.M19VSAC, P.M18VSAC, P.M17VSAC, P.M16VSAC, P.M15VSAC, P.M14VSAC, P.M13VSAC, P.M12VSAC, P.M11VSAC, P.M10VSAC, P.M9VSAC, P.M8VSAC, P.M7VSAC, P.M6VSAC, P.M5VSAC, P.M4VSAC, P.M3VSAC, P.M2VSAC, P.M1VSAC, P.CMTHVSAC INTO SORTED_P FROM P ORDER BY P.GEOSORT, P.TERR_CODE_IND, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_LOGICAL_ORDER;- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#27
|
|||
|
|||
Query doesn't always sort
Thank you Gary & John,
I will attempt to tweak the Excel-exporting macro to work for queries. I'll post a new thread if I come across any trouble with that Thanks again for taking the time. Sam |
Thread Tools | |
Display Modes | |
|
|