 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / December 2005Query does not order the same data in the same way
I am using MS Access as database for filtering, sorting and exporting data in order to prepare data for another program. This other program is rather old and requires sorted data in input file. For this I have being using Make_Table Query which filters data, orders it and saves resulting data in a new table FilterYEAR. This is SQL view of this query: SELECT NODE.ID, NODE.NAME, NODE.Urated, NODE.InYear, NODE.Ru, NODE.Area, NODE.Zone, NODE.Owner, NODE.OUTYEAR, NODE.INborder, NODE.OUTborder INTO FilterYEAR FROM NODE WHERE (((NODE.InYear)<=[GivenYEAR]) AND ((NODE.OUTYEAR)>=[GivenYEAR])) ORDER BY NODE.ID; When I open (run) this query every fifth to tenth time newly formed table FilterYEAR is not sorted at all or large chunk of sorted data is displaced from the middle to the top of the table. I’ve being using MS Office 2003, but this database was formed in Access 2000. Please can you help me? "Milkovi? Aleksandar" <Milkovic Aleksandar@discussions.microsoft.com> wrote: >I am using MS Access as database for filtering, sorting and exporting data in >order to prepare data for another program. This other program is rather old [quoted text clipped - 13 lines] >from the middle to the top of the table. Ive being using MS Office 2003, but >this database was formed in Access 2000. Please can you help me? How have you determined that the data is not sorted? If it was by looking at the new table in sheet view, then you are suffering from a misunderstanding. The data in a table is **NOT** sorted in any way. The **ONLY** way to sort data in a table is by using a query with an Order By clause. I see no reason for you to create a new table. Since you need to use a query to export the data, you might as well just export your above query and be done with it.  SignatureMarsh MVP [MS Access] Depending on the shell of the program I work with, I need to change data in the FilterYEAR before exportation (with Update Query). And that data has to be sorted ascending by NODE.ID. You may say that initial Make_Table Query which was mentioned in the first question is the base for subsequent changes of the data that must be done for other programs. Regarding to the fact that program needs sorted data, please help. > "Milkovi? Aleksandar" <Milkovic > Aleksandar@discussions.microsoft.com> wrote: [quoted text clipped - 26 lines] > need to use a query to export the data, you might as well > just export your above query and be done with it. As I said before, tables are **NOT** sorted and you can not do anything that assumes they are sorted. You may have a good reason for using a temporary table, but it's records will not be in any particular order. This is a fundamental premiss for all relational database systems, not just Access. Maybe you need to explain more about what you are trying to accomplish (instead of how you are trying to do it) an we'll see if we can figure out a way to get it to work. SignatureMarsh MVP [MS Access] Milkovi? Aleksandar wrote: >Depending on the shell of the program I work with, I need to change data in >the FilterYEAR before exportation (with Update Query). And that data has to [quoted text clipped - 31 lines] >> need to use a query to export the data, you might as well >> just export your above query and be done with it. My Excel table holds data about grid nodes. Each node is recognized with its unique ID. Some nodes work, some are in development phase or decommissioned and do not work. To determine if certain node works I have information about nodes year of entry and year of exiting. I need to find nodes witch work in certain year and nodes witch fulfills this requirement must be sorted by its unique ID. > As I said before, tables are **NOT** sorted and you can not > do anything that assumes they are sorted. [quoted text clipped - 42 lines] > >> need to use a query to export the data, you might as well > >> just export your above query and be done with it. Now I am really confused. Is this "table" in Excel? But Excel does not have tables, it has Ranges. An Access table does not have to be sorted to find a record using a date criteria. This kind of thing is done using a query like: SELECT table.* FROM table WHERE certainyear Between table.entryyear And exityear without using any kind of sorting. If you want to export sorted data from Access to Excel, export a sorted query instead of trying to export the table. SignatureMarsh MVP [MS Access] Milkovi? Aleksandar wrote: >My Excel table holds data about grid nodes. Each node is recognized with its >unique ID. Some nodes work, some are in development phase or decommissioned [quoted text clipped - 51 lines] >> >> need to use a query to export the data, you might as well >> >> just export your above query and be done with it. Imported data is in Excel and exported in *.txt file. Because of the text file I can not export query. It is essential that exported text file holds sorted data by ID because program witch uses text file is very very old and shuts down when data is not sorted. > Now I am really confused. Is this "table" in Excel? But > Excel does not have tables, it has Ranges. [quoted text clipped - 63 lines] > >> >> need to use a query to export the data, you might as well > >> >> just export your above query and be done with it. What do you mean you can not export a query to a text file? You can do just about everything with a query that you can do with a table including exporting. Just create a query to sort the data as needed and try exporting the query, it should be a no brainer. SignatureMarsh MVP [MS Access] Milkovi? Aleksandar wrote: >Imported data is in Excel and exported in *.txt file. Because of the text >file I can not export query. It is essential that exported text file holds [quoted text clipped - 70 lines] >> >> >> need to use a query to export the data, you might as well >> >> >> just export your above query and be done with it. I mentioned earlier that I use Make_Table query because later I must change data (in various ways for various export files) in newly formed table before exporting in text format. Queries that have the task to change data in sorted table do not disturb table order (I checked). So after I checked these queries I found that first query I use (Make_Table query witch selects and sorts needed data) do not work properly every five to ten time. I do not want (and I do not think it is needed) to create special table witch will only sort data in newly created table before I can proceed with data changes. > What do you mean you can not export a query to a text file? > You can do just about everything with a query that you can [quoted text clipped - 75 lines] > >> >> >> need to use a query to export the data, you might as well > >> >> >> just export your above query and be done with it. I can not overemphasize the fact that data in a table is not sorted. Only a query can present sorted data. If you need to manipulate the data in the temporary table, fine. Just use a query that sorts the temp table's data for the export. This may seem like an extra step in your mind, but it is necessary to meeting your objective. SignatureMarsh MVP [MS Access] Milkovi? Aleksandar wrote: >I mentioned earlier that I use Make_Table query because later I must change >data (in various ways for various export files) in newly formed table before [quoted text clipped - 86 lines] >> >> >> >> need to use a query to export the data, you might as well >> >> >> >> just export your above query and be done with it. Thanks on your help and on conclusion that it is only possible to export queries if you want sorted data for certain. It is a pity for that one more step you need to do before you can reach your goal. > I can not overemphasize the fact that data in a table is not > sorted. Only a query can present sorted data. [quoted text clipped - 93 lines] > >> >> >> >> need to use a query to export the data, you might as well > >> >> >> >> just export your above query and be done with it. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.