MS Access Query Order of results

Dear All,

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.

Many thanks,
Amy
klwnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Tables and queries are by definition, unordered sets.  If order is important, you must use queries that include an order by clause.

There is no need to make temporary tables that I can see based on your description, The queries that make them can be changed to select queries (including an order by clause) and exported directly to .csv using the TransferText method.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
klwnAuthor Commented:
Thank you for coming back to me so quickly.

The reason why I would like to keep the results in the CSV table in the same order as the source data in the XML file is because I would like to check the output results matches the source data and its easier checking the results when they are in the same order. The XMl source file could also contain around 600 records so makes checking eaiser if they are in the same order.

If i put an 'order by clause' on the resulting query, this wouldn't match the order of the data records in the source XML file? If i put an 'order by clause' on the generation of the CSV table, is there a way I can sort the source XML file in the same order to compare results?
0
PatHartmanCommented:
What is the order of the input file?  Is there an autonumber on the table you import to?  If you import to a table with an autonumber PK, the autonumber will reflect the import sequence and you can sort by that on export.  Once you make an autonumber the PK, you will probably still need to define a unique index based on data fields to satisfy business requirements.
0
PatHartmanCommented:
Did you not like my answer or do you not like the way relational databases work (all of them, not just Access)?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.