Update query with unique key

[Guest]

I have a DB with a todo list. Example Records are as follows:

Task ; Seq #

Wake up ; 1
Go to Bathroom ; 2
Brush Teeth ; 4
Take Shower ; 5
Go to Work ; 6

First, I want an update query to fix the missing number (Seq # 3 is
missing), so now I have:
Wake up ; 1
Go to Bathroom ; 2
Brush Teeth ; 3
Take Shower ; 4
Go to Work ; 5

Second, lets say I want to now brush my teeth AFTER I take a shower. Via a
form, I would change the SEQ # of "Brush Teeth" to "4" (or "Take Shower" to
"3"). Now I need an update query (or queries) to produce this:

Wake up ; 1
Go to Bathroom ; 2
Take Shower ; 3
Brush Teeth ; 4
Go to Work ; 5

Any Ideas????

 

[tina]

well, i don't see why you would need to update these records via an update
query - just do it directly via data entry in a form. also, don't worry
about the order of the records *in the table*; sort the record by the task
number in the form, or report - whereever you need to display the records.

take a look at this scenario:

tblTasks (this is just a list of all tasks)
TaskID (primary key, probably an Autonumber field)
TaskName

tblToDos (this is your to-do list)
ToDoID (primary key, probably an Autonumber field)
TaskID (foreign key from tblTasks)
TaskPriority (Number field, probably field size Byte)

your form is based on tblToDos. the TaskID field is bound to a combo box;
the combo box's droplist is based on tblTasks. for each record, choose a
task from the droplist, and then enter a priority number in the TaskPriority
field. if you accidentally skip a task, just add another record. if you want
to change the priority of tasks, just change the number in the Task Priority
field of the appropriate records. you can sort, and resort, the records in
the form, by the TaskPriority field. you can sort the records in a report,
on the same field.

hth