Moving columns in Access

[Ali]

I have queries that I run monthly where the results are not displayed how I
want them to be.

My queries are set up as follows: delete query, append query, and then a
select query that sorts the appended data in the table. The queries dump data
into a table, which I have set up in the order that I want to see the
information, but after I run the query to sort the data it is no longer in
the correct order.

I have created these types of queries many times before and never had this
issue, so I do not know what is going on. Any ideas on how to fix this would
be appreciated. Thanks.

 

[Jeff Boyce]

Ali

You don't mention which version of Access you are using.

I'm not understanding whether you are saying that the temporary table you
created has columns/fields in the wrong order, or if the query you use
against that table has the fields in the wrong order, or if the sort order
is incorrect.

More info, please...


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

Your subject line and you question content are confusing me.
Are you saying the order of the columns(fields left to right) is not what
you expect or are you saying the records are not in the order you want?

If it is the columns, then except for displaying the data to the user, the
order makes no difference. And, if you are allowing users to see the table
directly, you are begging for disaster. You should use at least a datasheet
form to show the data and you can arrange the columns in any order you want.

If it is the sort order of the records, records are stored in tables in no
particular order, so you will not even always get them back in the same
order. In this case, you should also use a datasheet form, but rather than
the table being the record source of the form, you should use a query based
on the table and set the sort order of the query to whatever you want.

 

[Ali]

I am using Access 2003. The temporary table has fields in the wrong order.

 

[Klatuu]

Back to my original point, Ali. What difference does it make. The order of
fields in a table is irrelevant. Users should not be give access directly to
tables.
Access is not a spreadsheet program, it is a relational database.

 

[John W. Vinson]

My queries are set up as follows: delete query, append query, and then a
select query that sorts the appended data in the table. The queries dump data
into a table, which I have set up in the order that I want to see the
information, but after I run the query to sort the data it is no longer in
the correct order.

Creating the temp table is the source of your problem, in addition to being
unnecessary, bad design, bloating your database and risking corruption.

Just base your reports and forms *directly on the Select Query* rather than
taking the extra step of creating a new table.

If you're showing users the table or query datasheet... don't. That's not the
purpose of a Table; user interaction should all be done through forms for
onscreen viewing, or reports for printing.

 

[Jeff Boyce]

If the order of the fields/column output by your query is incorrect, change
your query. The order of the fields/columns in your table is irrelevant.

Regards

Jeff Boyce
Microsoft Office/Access MVP