Microsoft Access 97

  Lawnders 15:07 09 Jan 2004

I have a table in Access 97 containing 11 fields arranged in columns, each column containing a list of words. I want to arrange the words in each column in alphabetical order. I can do it one column at a time but not all 11 simultaneously. I will need to add new words to various columns and would like them to find their alphabetical places. Can I arrange words alphabetically in multiple columns in a query also?

  GrahamP 15:36 09 Jan 2004

If you mean sort the columns independently, then the answer is no. Each row is a record and no matter how you sort them, the fields in that record, as you read them left to right, will remain in that record.

  GrahamP 16:19 09 Jan 2004

If you can create separate tables side by side in Word, you ought to be able to sort them independently. Columns in Word might also allow it. Beyond that you are probably looking at doing something clever behind the scenes.

  short-circuit 21:35 09 Jan 2004

The table you have built contains records of Words in the 11 Columns. What is the relationship between the words in the separate columns? Are any of the Words in the columns repeated? What is the function of your database? I was trying to imagine exactly why you had written this table and what information you might be wanting to extract from the data records and the only possible idea I could come up with was a multilingual dictionary. Is that what you are trying to do? The only way forward, I can imagine on the current information is to reconstruct this as a relational database with 11 separate but related columns.

If you could give a little more information about what the records in your table deal with and what information you hope to extract from the data, I would try to offer some suggestions for how you can achieve the task.

  skeletal 22:22 09 Jan 2004

My understanding of your question leads me to say that your are attempting the impossible. However,if you mean: how can I sort 11 columns of data that have no connection to one another; then the answer, along the lines of the Word suggestion, is to use Excel. Drop the data into a worksheeet, select each column individually, and do a sort. Excel is limited to only 65,000 or so rows though.

However, I don't think I understand exactly what you are trying to do!


  Lawnders 19:32 10 Jan 2004

Exactly what I am trying to do is create a database for crossword words in order to compile a crossword. Each column is for words of a certain length ie 3 letters, 4 letters etc. So there are 11 columns for word lengths from 3 to 13 letters. When compiling a crossword I want to be able to search in my database, as a query, for words with certain letters in certain places. It would be useful to be able to print out the whole table with each column in alphabetical order to check for the existence of a word and so to enter one, when out and about without the computer. These words are on a specialist subject and so will be limited in number.

  skeletal 21:24 10 Jan 2004


So, I think, you want a method of listing all 11 columns, in order, of words where your letters are in the same place in each word in each column where there is a match. I think, I understand, but it's hard to describe!

However, on to the solution. I think that, if I'm on the right lines, you cannot use one table. This is because, as has been suspected, each word has no relationship with any other, apart from after you have done the query.

Thus you need 11 tables.

In simple terms; if you now make 11 queries (one per table) with the desired letters in the correct place in the search criteria; you will now get 11 results that you can sort in alaphabetical order.

You could now print out all 11 and job's done!

But, of course, that would all be a right pain!

I think you could do all that in one go, but I need to think about it a bit more (unless someone else can think better/quicker than me!

Could you confirm that my method would work, all be it very impractical? It would be a bit of a nuisance to devote years of time to solve the wrong problem!


  wee eddie 22:01 10 Jan 2004

Single Column

Query > All Words longer that X, shorter than Y

Query of Query > All words with the "whatever" letter Z

  skeletal 23:07 10 Jan 2004


In principle seems OK. I was thinking of the next step (if I have understood the problem) of how to simultaneously generate the required outputs in 11 columns, for ease of examination by Lawnders. I can see a way forward with 11 tables/querys, but not with one column (or not so easily).

However, there are as many programming solutions as there are people, so you may be on to a better way we eddie!!

As a matter of interest we eddie, how were you thinking of performing the "All words longer than x etc"? Possibly an IIF? I'm always interested in how others play with is so huge I admit to only knowing about 0.1% of it (i.e. only 1000 pages of text books!!!).

Lawnders, let us know if the assumptions are correct, you may get two solutions to your problem!!

Oh, by the way, I have Access 2000, but I think there are many similarities to 97...hopefully enough.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone