I have created an MS Access database which I use to import and convert an XML file into a required text file format.
(1) The XML file firsty gets imported into a temporary table (DocumentDetail).
(2) I then have two make table queries.
(3) The first make table query converts the data from the temporary table (DocumentDetail) into the required data format and outputs this into another temp table (CSV_Export).
(4) The second make table query then takes the CSV_Export table and picks out the fields which are required for the text file output and outputs them to a third temp table (CSV).
(5) The user can then right click on the final table (CSV) and export this to a text file in order to import it into their third party system.
I have a macro which runs points (3) and (4) above and then clears the first temporary table (DocumentDetail) in readiness for next months XML import.
However sometimes when I open the last table 'CSV' to check the results matches the data in the XML import source file, the order of the data will remain in the same order as in the XML import file, but other times the order is randomised?
My question is, is there a way to keep the order of the results in the CSV table in the same order as the source data in the XML file? Do you know why the order changes sometimes when the same queries are run each time and there is no sorting specified in the make table queries?
Your advice on the above would be greatly appreciated.