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 |
#1
|
|||
|
|||
Query 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? |
#2
|
|||
|
|||
Query does not order the same data in the same way
|
#4
|
|||
|
|||
Query does not order the same data in the same way
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. -- Marsh 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 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" 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 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? "Marshall Barton" wrote: 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. |
#5
|
|||
|
|||
Query does not order the same data in the same way
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. "Marshall Barton" wrote: 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. -- Marsh 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 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" 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 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? "Marshall Barton" wrote: 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. |
#6
|
|||
|
|||
Query does not order the same data in the same way
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. -- Marsh 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 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. "Marshall Barton" wrote: 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. 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 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" 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 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? "Marshall Barton" wrote: 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. |
#7
|
|||
|
|||
Query does not order the same data in the same way
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. "Marshall Barton" wrote: 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. -- Marsh 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 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. "Marshall Barton" wrote: 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. 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 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" 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 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? "Marshall Barton" wrote: 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. |
#8
|
|||
|
|||
Query does not order the same data in the same way
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. -- Marsh 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 sorted data by ID because program witch uses text file is very very old and shuts down when data is not sorted. "Marshall Barton" wrote: 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. 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 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. "Marshall Barton" wrote: 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. 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 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" 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 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? "Marshall Barton" wrote: 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. |
#9
|
|||
|
|||
Query does not order the same data in the same way
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. "Marshall Barton" wrote: 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. -- Marsh 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 sorted data by ID because program witch uses text file is very very old and shuts down when data is not sorted. "Marshall Barton" wrote: 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. 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 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. "Marshall Barton" wrote: 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. 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 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" 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 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? "Marshall Barton" wrote: 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. |
#10
|
|||
|
|||
Query does not order the same data in the same way
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. -- Marsh 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 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. "Marshall Barton" wrote: 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. 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 sorted data by ID because program witch uses text file is very very old and shuts down when data is not sorted. "Marshall Barton" wrote: 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. 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 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. "Marshall Barton" wrote: 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. 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 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" 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 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? "Marshall Barton" wrote: 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. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I save an access document in word document? | cmartin | General Discussion | 2 | September 13th, 2005 11:26 PM |
AVG Function in a Query | JohnL | Running & Setting Up Queries | 5 | December 18th, 2004 05:52 AM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM | |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |