>But isn't this limitation of Access even more of an arguement for teaching Access hobbyist to design data fields in a logical fashion? It really seems the problem is being framed as "How do I keep the leather on my shoes from getting messed up when I swim?" < bg >

Yes, your point is good, and I'll try to explain here. But just to clarify, I'm not exactly a "teacher" here. We already have the 50,000 or so Access users that've created lengthy 1, 2 or 3 column lists of data, some in the 1000s or even 10000s of records, but the largest should not be more than about 50000 records.

So, 1) some people have already developed a way to get the desired sort using only 1 table, but with 2 or 3 columns. Their problem is with difficulties in doing (form) data entry, primarily. They're not happy about this aspect, anyway, even though they can get a good sort once they get the data entered (it's all manual, these CatNumbers are never very sequential, unfortunately, there are gaps all over the place between the numeric portions).

Then 2) there's another large group that created 1-column separate tables for each alpha (about 15 in total, plus the numeric only table), then just entering numerics in each of these tables so they can get the sort. Again, they are not happy with this solution, they still cannot build a nice list of everything easily. And they must switch back & forth between tables constantly, ugh.

What everyone wants is *1 table* with *1 column* for all alphanumeric entries, which can be sorted as I demoed.

These hobbyists generally are bewildered why they spent the $$ on Access, only to find it wouldn't do what they wanted. My requested task is to try to find a solution where they can use 1-column, 1-table, and get the desired sort.

Since an index expression (Plan A) won't work (and I imagine at least a few of the more experienced Access people have already discovered this and I just haven't heard from them yet), I will have to try a plan B, plan C, etc.

Plan B is to try to use SQL for an ordered result set, based on the 1-col/1-table method, which is unanimous (no one wants to use more than one column or table for the data-entry).

A Plan C might be Hilmar's suggestion about a 2nd "calculated" column, if it can be done transparently (after an instructional setup phase, I mean, that part I can publish happily if it can be done).

But, basically, you're quite right. A professional would carefully do the DB design, and then test along the way to ensure all is well. But these are not pros, just hobbyists with little or no tech experience, and they already have entered large amounts of data before they found they couldn't get a good 1-column sort. They've done all sorts of bizarre things (like re-enter all the data in the exactly sorted form (ouch!).

I don't know if I'll find a reasonable solution for existing data, other than to purchase a tool like vfp and migrate the data where one of our vfp indexes can be used. I will at least mention the flexibility vfp offers, might bring in a few new vfp users, even.

And 2nd, I may do much as you say: for those constructing new Access data lists, explain the Access limitations, and give as many plausible suggestions as I can come up with, on how to set up an Access DB such that they can sort easily. No one has done this yet, hence all the whining and confusion among our hobby.

Probably before I deal further with Access, though, I will tackle Excel, which is at least, and probably more, popular than Access for creating these lists. Since almost all the Access people have Excel, if I find a reasonable solution in Excel, i will simply go with that, and provide instructions on exporting from Access to Excel, then sorting in Excel. I guess that's Plan D?

So, I haven't run out of ideas just yet, and possibly may find a decent solution somewhere within these ideas. If not, well, I'll still do the article and explain the problems of getting data sorts in Access and/or Excel.

Many in my hobby want to know more about this touchy "sort" subject and options for dealing with it, so I'm sure I'll have plenty to write about. I'm just hoping I can come up with some fairly positive ideas that aren't too difficult for dealing with the users with a lot of current data.

For newbies, though, I most likely will write about it along the very line you suggest, which is to emphasize testing design and functionality *before* entering large amounts of these catalog descriptors.