Append Queries - Sorting Issue (1 Viewer)

[jctuts]

Hi,

I have a macro that runs a 'make table' query followed by a series of 'append queries' each time the macro is called. I require the data from each append query to be pasted below the existing data already created by the original make table query and the append queries that have already run. The issue that I am having is that the data in fact appears to be appended in an unpredictable way (ie sometimes I run the macro and find that data from the append queries is below the data from the make table query, at other times I run the macro and find that data from the append queries appears above the data from the make table query. Unfortunately, this causes problems with downstream queries, which rely upon finding the last row of data subject to various filters. Can anyone help me understand how Access determines the sorting/positioning of new data appended to an existing table, and therefore resolve this issue?

Thanks in advance,

jc

 

[tehNellie]

A record's position in the table itself isn't important. You should order your records as desired with your Select Query. If there are no suitable candidates for this sort at the moment you could add a datetime column with a default value of now() which will, in effect, create a timestamp of the record's creation and use that to sort by in your select query.

 

[jctuts]

Good idea - I'll add a time stamp. Thanks for your help.

 

[boblarson]

The issue that I am having is that the data in fact appears to be appended in an unpredictable way (ie sometimes I run the macro and find that data from the append queries is below the data from the make table query, at other times I run the macro and find that data from the append queries appears above the data from the make table query.

For your information, Access does not store data in the tables in any meaningful way. They are not ordered and what happens is people happen to see that it appears to be ordered when they first start using it, as it a lot of times looks like Access is storing data in the order added, but then all of a sudden reality hits when you start seeing exactly what you're seeing.

So, in order to give order, you must order the tables yourself by utilizing queries. Even if you do have a date/time stamp in the table, the table itself will not necessarily present itself in an ordered format. So, you work with queries to impose such order. And, for touching data, your users should work in forms, which should have queries as their underlying recordsource (they can be ordered) and they should really not touch the data directly by opening tables, and probably not even queries directly, as you give up a lot of control on how data is presented and validated if you allow them such access.