[Resolved-somewhat] Sorting on multiple fields in Access 2002

[A441OTA]

Good morning,

If you need to automate a Word mail merge make sure that you have a primary key or indexed field to indicate the order in which you want the records to go to Word. If there is no primary key or index Word will use the AutoNumber field (whether you created one or not, it's there of course!).

I have a database that I have to merge, but it is not a "normal" database so there were no keys or indices. Word was using the order in which the tables were created, even after doing a manual sort on the table. However, after indexing the appropriate field Word used the indexed field instead of the order the table was created in. This prevented having to deal with the multiple fields I initially was trying to sort by.

Thanks to everyone especially Pete and Vince for your time and advice.

Have a great weekend!!
Mary



***********Original post ******************
Hi everyone!

I tried searching but did not have any luck finding something that worked.

I have a program that takes an Access 97 (Originally it's an Access 97, but I converted it to a 2002 formatted file for now) file and rips it apart and puts it back together again so that I can use it to send the correct data to Word as a mail merge. Everything works fine except that the sorting I'm trying to do is not working. There seems to be a problem with my Order by statement when I create the final main table. Here is the SQL statement I'm using:

'Access 2002, VB 6, DAO 3.6 at this point
 
sql = "SELECT * INTO " & NewSurvey & "_QstData FROM " _
& NewSurvey & "_Main ORDER BY " & NewSurvey _
& "_Main.[Tab_Order], " & NewSurvey & "_Main.[Label_Order]"
db.Execute sql

I have tried this many ways including taking the specific reference to the table out (so that the fields were identified as [Label_Order] without the table name)

After the code runs, the new table is there but not sorted.

I also attempted, to no avail, to open it as a recordset through ADO and used the .sort method as follows:

'Access 2002, VB 6, ADO 2.7, Jet 4.0, MDAC 7
 
With rsSurvey
     .Sort = "Tab_Order, Label_Order"
End With

I can do this manually through Access if I go to the table properties and set the Order by property to these two fields -- can this be done through code?

There are no indexes set, there are no primary keys (too much duplicate data -- at the point I'm trying to sort on the two fields is the first time the data is recognizably "distinct" records if both fields are used to sort by). Both sort fields are numeric.

Any suggestions would be greatly appreciated -- I'll keep searching in the meantime and checking back to see if there is a need to clarify anything for anyone that is willing to give this some thought (and thank you in advance if you are!!).

Thanks,
Mary