Append Query in Sort Order

[was1]

I'm have an Append Query that I want to append to a table in a particular sort order. When I run it as a select query, its in the correct order. Can someone help me get it appended in the correct order? I have no VB knowledge.

thanks,

 

[rac2]

You may be able to successfully insert rows in some order, but you really cannot count on them remaining in order. The order of rows in a table has no meaning and is under control of the database engine, not us.

Ordering, to be effective, should be done in the SELECT statement. You may wish to add a column to the table for the specific purpose of determining the order. Then you can always retrieve the rows in order by sorting on that column.

 

[was1]

thanks, if i create a make table query and insert a column for the order, how do i create the "order" field as a number field opposed to a text?

 

[Golom]

Is there currently an "order" field in your source table and, if so, what does it contain?

If there isn't an Order field in the source table then, after you have run the Make Table, add an autonumber column as the "Order" column ... but check out the dangers and limitations of autonumber columns first.

 

[rac2]

if i create a make table query and insert a column for the order,

Hmmm. I wonder if you really mean to create a Make Table query? Such a query, a SELECT INTO statement, is used to produce a new table from existing tables by combining columns and obtaining a subset of rows from one or more existing tables. Probably for temporary use or for a report or for export. It seems likely that there is an existing column which could be used for sorting the temporary table. Simply include that column in the column list in the SELECT INTO statement. Golom advises this.

how do i create the "order" field as a number field opposed to a text?

Text and numbers can both be used to order things. As in alphabetical and numerical sorts. If you have some existing data which determines the order then it can be used regardless of whether it is text or number. If there is no existing data, then you must add that information to the database. If this is a one-time-only operation and the new table will never have rows added or deleted after it is created, then Golom has the answer.

 

[was1]

it is and isnt a one time thing. i have a list of records that i need to rank based on a priority calculation of various different fields. Currently when i run the query which includes my different sorting fields, it sorts in the correct order. I then need to add a ranking to each record based on the priority. I was thinking of the following:

1. Create a Make Table Query (to be able to export) TBL1
2. Create a row in TBL1 named Rank
3. Add ranking numbers to TBL1
4. Export table.
THEN.... when new records show up the ranking order may change so then i would...

1. Delete TBL1
2. Append to TBL1 (this is where the sort gets out of order)

or is there a way to add a my ranking number in the query itself? i would like to add an autonumber but when i rerun the query that the autonumber starts with 1 again.

did i confuse everybody now?

thanks for your help....

 

[rac2]

rank based on a priority calculation of various different fields.
...
Currently when i run the query which includes my different sorting fields, it sorts in the correct order

That is how it is done. You do not need a priority ranking column. Just sort by the expression which calculates the priority.

BTW. In database terminology, rows in a table are the horizontal lines, each row has the data for a different thing. The columns are the verttical lines, a column is some attribute of the things stored in the table. So the Rank you mention in point 2 would be a column in TABL1.

To put it bluntly, forget about the autonumber column, it is not the solution to your problem.