[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

[PilgrimPete]

I think your problem is that Access (as with many other databases) is a law unto itself, and will generally store data as it sees fit, unless you add indexes to give it some 'guidance'.

Can you add an index?
Or can you just sort the data when you extract it out to Word?

[A441OTA]

Hi Pete!

I actually have the fortunate capability to do whatever I need to do in this database to make it work!! So if adding indices is the answer then sobeit! Someone had suggested that to me last night so I'm trying to figure out how to do this with the multiple fields right now.

The sorting has got to be done on the main table before I create the individual tables -- subsequent tables created in Access "supposedly" inherit sort order and indices. It seems that Word will pull the order that the tables were created in, like it's using the autonumber field.

The program that I am writing simply uses a database to collect a little information from the user upfront, then uses this information and the data that is sent later (this is a db that I have to "rip apart" and put back together again to make it useable!!). The less user-intervention the better in their book -- it's what I have come to affectionately refer to as abnormal database programming -- it seems to me that if I were simply programming so that users could enter information and get information I would be in heaven!! I have to take a database (which is a very relative term -- it's really an Excel spreadsheet sent to Access, with empty placeholder rows and information jammed in fields that doesn't belong because that's where they wanted it to appear in the spreadsheet! If that makes sense to you!). Later down the line the information that I currently collect upfront from the user will actually come in from a datadump (they are currently building that system with another contractor!!).

All of this information needs to get to Word in a predetermined "shell" report -- thus the mail merge! I couldn't find a way to use a temporary recordset as a mail merge datasource so I had to resort to building a separate table for each question (this is data from a survey). Later I will write the code to remove these tables after the mail merge is performed, but I'm using them to verify the data for now (which is how I found this problem!).

Sorry, I'm rambling again... I'll let you know how it goes. Need to go search on setting indices on multiple fields. Then again, maybe if I just sort on one field and allow duplicates then that should be fine... that might be more efficient, I'll try that first!

Thanks,
Mary

[PilgrimPete]

Wow! That sounds complicated! Rather you than me.

What I meant about sorting the records before extraction was to link to a view/query rather than the table directly. Then you can build all the sorting you like into the view.

Do you need to set the indices through code, SQL or just manually?

[A441OTA]

Awwwwwwwww, are you sure you don't want to take this over? LOL LOL LOL I wouldn't wish this project on my worst enemy right now!! I still consider myself a novice, and database programming really scared me way back when I took classes, but I would give anything to write a "normal" database program right now!!! I am actually going to try one for my Class reunion committee -- we had a horrible time tracking people down and the lists I kept getting from the committee were not "kept up" so I'm going to try to write a program to keep track of all our classmates and update their information. We now have a website so maybe if I can tie it to the website each classmate can update their own information. It will definitely be a challenge, but it will definitely be a "normal" type of program!!!

I'm not sure what you mean by linking to a query or view first... do you mean create a temporary recordset in the memory in the correct order (using "order by") then making a table from that? Would that work? I'm still definitely a novice and some of these concepts are hard for me to grasp (I've been a "user" for so long I don't quite think like a programmer yet!!).

Everything has to be automated as much as possible -- they want user-intervention only if it is absolutely necessary. I fear it will get more complicated before it gets better! I'll just use this as an opportunity to "grow"! LOL

I managed to get all of the coding for indexing working (I used DAO on my main table because that code was written when I hadn't quite grasped ADO yet and it worked so I left it in there!). The code creating each individual table is written in ADO and I wrote a SQL statement against commandtext to set the index. Both ways are working just fine... the jury however is still out on the document creation. I am running a test right now but it takes about 1 1/2 hours (there are 115 individual items being created -- 115 new tables used as the datasource for 115 Word mail merge documents). Last week when we manually sorted a table that was created out-of-order we ran a manual mail merge on it and Word used the order the table was created in. Today, after I got all the code working for the index, I found some tables that were not created in the right order by assigning an autonumber field to the table (they were in the correct order visually by the indexed field) and then ran a manual mail merge and Word used the indexed order......... I hope that's all it takes because I cannot figure out how to create a table in the correct order (my program didn't like "order by" in DAO or ADO!!!)

Here I am rambling again..... sorry!!!

I'll post my results, work-arounds, and/or resolutions after I have finalized what they are!! Just-in-case (and God forbid) somebody else has to do something like this!!!

Thanks,
Mary

[PilgrimPete]

Originally posted by A441OTA
I'm not sure what you mean by linking to a query or view first... do you mean create a temporary recordset in the memory in the correct order (using "order by") then making a table from that? Would that work? I'm still definitely a novice and some of these concepts are hard for me to grasp (I've been a "user" for so long I don't quite think like a programmer yet!!).

No, what I meant was to create a stored query on the database, that took all the data from the newly created table and then sorted it appropriately. Then when you are selecting your datasource for the mailmerge you can select the query instead of the underlying table.

But, having read the rest of your post, that sounds like a bit of a pain if you need to create 115 tables and then 115 queries - you'll be safer creating the indexes I think - and they should improve performance... although (and I'm rambling now) creating the index after you have populated the table might not be very quick - it depends on how much data you've got I guess.

[Ecniv]

Hi,

I was wondering - if you can do whatever you like - why not make a neat front end to the database yourself so the entered data has to be correct. Otherwise you'll be forever trying to correct whats been input (or not).

Also this means you get to make a neat front end bit. User friendly etc...
Can you specify sql statements in a mail merge (not done one - no one needed it... yet)

Vince

[A441OTA]

Hi Vince!

Thanks, I definitely understand what you're saying... however, my control over the users is nil -- they don't want to do anymore than they have to!!

I have a front end that initially collects some data from the users because the report that is the ultimate result is run on a database that is created by a SAS macro and does not include all the information that my client wants in the report. This forces them to at least put in some initial information and I have this part working and validating so that the information is "good". It's the database that SAS is creating that is the problem. It is a total mess and not even close to being an actual database. The client did not want to have to "straighten it out" through prompts so I had to write code that rips it apart and puts it back together into a useable format (if anyone is interested in seeing the before and after let me know, I'll post it one day!!!) without the user. While it's putting it back together it uses the information that was input prior to bind it all together. My limitation is the desire of the client to keep the user out of it as much as possible!!

Not to mention that nothing is standardized and everything varies every single time they do a survey. The database I receive will have a different amount of columns everytime with different names, etc. There are a few "core" fields that I have strongly suggested they don't change or it won't work (the first step towards standardization!!). I am a contractor so I have no control over the processes -- I have been tasked with creating automated reports from a process that has been going on for years (and years, and years, etc.).

I had a tough time with the mail merges because of all the variables. The datasource in Word had to be trimmed down to no more than two variables. This was not documented anywhere and I only figured this out through trial and error, and I couldn't find any other way around this. Word can use a query for a datasource, however, I couldn't figure out how to set up a stored procedures to pass numerous parameters to and it also seemed more efficient in this instance to simply create a new table, do the mail merge then delete the table. Mail merge will take a "simple" SQL from what I understand but couldn't get it to work with more complex variables.

Indexing worked (Thanks Pete!). I was still concerned because the order which the tables were created was what Word used when we did this manually on our test last week. However, Word quit using the autonumber field once I had the index set... I guess the logic is "hey look, she did something special to that field, maybe I should use that one instead"!! It makes sense after-the-fact of course!

So, okay, I have this step working now... next step, looking at how to deal with the final table!! And then, how to take all of the code I have written so far and pack it into a module or something because it will only work with one type of Survey -- I have to rewrite it to deal with the next type of survey!! I am still a novice and consider myself a beginner so I understand this concept, now how to do it, that's the trick!! I have time to research this one, so until the next post.... THANKS EVERYONE!!!