Not Sorting within Queries

[brendanolear]

I have 20 or so Queries all based on similar criteria. The first Column of each query has numbers 1 to 500 - all Queries are "Make Table" and all Queries have an "Ascending" sort within the first Column yet when I run via a Macro not all Tables are sorted correctly.

I can run the macro on the same data time after time and each time at least one or two of the tables will not be sorted correctly.

If I run the queries independently they all sort correctly.

Do I need some kind of pause entered between the 20 "OpenQuery" that run?

 

[Joe4]

I am not quite sure what you mean when you say "not all tables are sorted correctly". The sort order of a table is not necessarily the same as the sort order of a query.

If you would like to view the data in a table sorted in a particular order, you can use a query to do that.

 

[brendanolear]

The 20 Queries I run are all "Make Table" Queries and each table that is created has within col A number range 1-500.

I wish each table to be sorted sequentially and have an ascending sort on each query in order that when the table is created the table will be presented in sequential order.

of the 20 tables that are created it is normally the case that 18 or 19 are sorted without problem, however there is always one, if not two, tables that are not sorted sequentially. It is never the same one or two tables that have the anomaly, but can be any random report from the 20.

I believe this is as a result of processes overlapping and think some form of wait or suspend command would help between each query.

Wod do I incorporate such a command directly from the "Macro's"-"Action" options available?

 

[Norie]

I've never heard of such thing as process overlap when running queries in Access.

Records in Access aren't stored in any order?

If you need them in a particular order use a query.

What I would be more worried about would be that the expected data are being returned to the new tables.

Is there a reason the tables need to be in a specific order?

Also, why are you using make table queries?

Is it a performance issue?

 

[brendanolear]

Norie, I have used Make Tables as each of the tables are exported to excel and sit within a massive excel file which has numerous sheets. Each sheet must run sequentially 1-500 in order to present data correctly.

My issue is that I have to revisit every sheet to ensure sort is correct, and yes I could data sort within excel using a macro, however had hoped the access sort on ascending would have done job in first place, but it doesn't. any guidance appreciated.

 

[Norie]

Brendan

How are you importing/exporting to Excel?

You could just import/export the queries to Excel and the sort order should be kept.

If you need the make table part of the process you could import/export the tables via simple queries like this.

SELECT * FROM Table1 ORDER BY [FieldToSortBy]

There's probably some other options as well.

Oh, and it wouldn't be too hard to do the sort on Excel with a little bit of code.

That's if each table requires the same sort if course.

 

[Ruddles]

<deleted>