MAKE TABLE query sorting confusion

  • Thread starter eatc7402 via AccessMonster.com
  • Start date
E

eatc7402 via AccessMonster.com

I am trying to figure out if there is a problem with my usage of MAKE TABLE
queries.

I have some data that consists of 20-40 records EACH of yearly records
sorted by YEAR. I have a query that displays this data correctly SORTED by
year (descending). I have currenrly approx 700 records.

When I convert the above query to a MAKE TABLE version of the same query,
I get some weird results. The data that results in the table created is NOT
SORTED the same as the select query was.

Should it be???

I have then created approx 15 APPEND queries (doing one years data at a
time) that I then execute one-at-a-time. This results in a correctly sorted
table creation.

If I create a MACRO to run the 15 APPEND queries one after the other,
I get the similar screwed up sorting. It seem like speed (maybe??)
has something to do with it.

I am running Windows XP, with 1meg of memory so I don't think
that is the issue. BTW, I am running Access 2002, Sp3.

I don't quite understand why this is happening.

eatc7402.
 
D

Duane Hookom

Tables don't really have a sort order. Records are like marbles in a bag. If
you reach in and grab one blindly from the "top", it could be any record.
Sorting occurs in queries.
 
J

John W. Vinson

I am trying to figure out if there is a problem with my usage of MAKE TABLE
queries.

I have some data that consists of 20-40 records EACH of yearly records
sorted by YEAR. I have a query that displays this data correctly SORTED by
year (descending). I have currenrly approx 700 records.

When I convert the above query to a MAKE TABLE version of the same query,
I get some weird results. The data that results in the table created is NOT
SORTED the same as the select query was.

Should it be???

Not especially. A table HAS NO ORDER. It's an unordered "bucket" of data; if
you want to see records in a certain order, then you must - no option -
include a field to specify that order, and create a Query sorting by that
field.

Note that if you're doing a make-table just in order to create a Report you
needen't. A Report can (and usually will) be based on a Select Query selecting
a subset of the data. Changing the select query into a MakeTable query just
adds another step, a lot of overhead, and (just for example) problems with
sorting.

John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top