Microsoft Access 2003 sorting in a cell

[Doug Frome]

I use Microsoft Access 2003 with XP. I have a Memo field with a pile of coded numbers that look like this :-

1399;1705;1720;3029;2293;2307;2308;2349;2376;1262;2385;2402;2448;2455; 1878;2507;2571;2587;2605;2621;2677;2678;2692;2930;2962

They are all separated by a semi colon and 100 is the most amount of numbers in the cell.. I need to figure out a way of putting them all in ascending numeric order, so they would look like this :-

1262;1399;1705;1720;1878;3029;2293;2307;2308;2349;2376;2385;2402;2448;2455;2507;2571;2587;2605;2621;2677;2678;2692;2930;2962

Is there an update query that I can use for this ? even if it means placing them in an adjacent memo field ?

Cheers,

Doug

 

[Pat Hartman]

Welcome Aboard

Spreadsheets have cells which are addressed by a specific row and column so position is meaningful. Tables have columns. If you want data from a single instance, you need to know the name of the column and the value of the primary key so you can find the row.

The major problem is having them all in one field. You should have a second table and that table would include a primary key, a foreign key (the ID of the record in the main table that these items belong to) and a row for each individual value. With this correct structure, you would use an order by clause in a query to order the values.

With your current schema you have a problem that is never ending. Each time the field gets updated, you need to sort the items. You will need to search Google for VBA sorting algorithms. You will need to put the algorithm in a function and pass in the field with the mushed data. You can create an update query that calls the sorting function to order all the existing data. This query may take a long time to run. Then you would call the function from your form in the control's AfterUpdate event to sort the items each time the user updates the field. If this is disturbing to the user, call the sort function in the FORM's BeforeUpdate event so the field isn't sorted until just before it is saved.

My advice - FIX the table!!!