Update Query
- Thread starter jmmm
- Start date
Thanks for the welcome 
The answer, as best I can muster is,
I have a number of records in a table which are in the wrong order.
I can select them in a query and sort them into the 'right' order.
But I want to update them, so they are always right when viewing the whole table.
I must say, I have already now done this (in a long and tedious way) but felt there must be a more efficient method using an Update Query.
The answer, as best I can muster is,
I have a number of records in a table which are in the wrong order.
I can select them in a query and sort them into the 'right' order.
But I want to update them, so they are always right when viewing the whole table.
I must say, I have already now done this (in a long and tedious way) but felt there must be a more efficient method using an Update Query.
Atomic Shrimp
Humanoid lifeform
- Local time
- Today, 12:58
- Joined
- Jun 16, 2000
- Messages
- 1,954
There's no reliable way to control the order of records in a table - tables are by definition unordered sets. But there's also no need to control the order of records in a table.
If you want to interact with the records in a specific order, you should be doing so via a form (well, you should always use a form to access a table anyway, regardless of the ordering question).
If you want to interact with the records in a specific order, you should be doing so via a form (well, you should always use a form to access a table anyway, regardless of the ordering question).
Bearing in mind what Atomic Shrimp said about tables being unordered lists and the need to interact via forms, if you do want to view the table in a particular (say for design purposes) then choose the field to order the table.
Click on the header row of the column and choose the sort order.
Manually sorting them will lead to frustration because new records will be written randomly into the table.
Click on the header row of the column and choose the sort order.
Manually sorting them will lead to frustration because new records will be written randomly into the table.
Atomic Shrimp
Humanoid lifeform
- Local time
- Today, 12:58
- Joined
- Jun 16, 2000
- Messages
- 1,954
That's right - either build your form based on a query that pre-sorts the records the way you want, or use the Order By property in the form iteself.
If your sorting needs are really complex*, you may need to create a synthetic sorting field in your table and use that.
*real-world example of 'complex' sorting needs: I created a db to maintain a company price list for pet books - they wanted it basically sorted by title (because the titles were formatted as 'Siamese Cat, How To Care For Your' and 'Gerbil, Buying And Choosing A').
But there were exceptions - for example, a book called 'Bunny Guide' had to appear with the books beginning 'Rabbit...'
I added a field 'synthsort' to the table - in most cases, it was completely empty, but in the case of 'Bunny Guide', the field contained 'Rabbit AAAA'.
Then I set my forms, reports, etc to sort on [synthsort & title], so as far as the sorting process was concerned, it was handling a value consisting of 'Rabbit AAAABunnyGuide', which put it at the top of the list, above 'Rabbit, Caring For Your'
If your sorting needs are really complex*, you may need to create a synthetic sorting field in your table and use that.
*real-world example of 'complex' sorting needs: I created a db to maintain a company price list for pet books - they wanted it basically sorted by title (because the titles were formatted as 'Siamese Cat, How To Care For Your' and 'Gerbil, Buying And Choosing A').
But there were exceptions - for example, a book called 'Bunny Guide' had to appear with the books beginning 'Rabbit...'
I added a field 'synthsort' to the table - in most cases, it was completely empty, but in the case of 'Bunny Guide', the field contained 'Rabbit AAAA'.
Then I set my forms, reports, etc to sort on [synthsort & title], so as far as the sorting process was concerned, it was handling a value consisting of 'Rabbit AAAABunnyGuide', which put it at the top of the list, above 'Rabbit, Caring For Your'
Thank you all for the replies. This was a further sort that was
wrong within an already sorted set - because there were numbers in a text field not
seen as numbers. Now fixed... I conclude an Update Query by itself couldn't do it.
wrong within an already sorted set - because there were numbers in a text field not
seen as numbers. Now fixed... I conclude an Update Query by itself couldn't do it.
