Swapping data between two rows

[cprobertson1]

Good morning,

I thought I had an easy project - but I've hit a bit of a wall in terms of how to best do this.

I'm developing a scheduler application, and ultimately need to be able to swap two rows in the schedule - literally just shifting them up or down - simple stuff... except I've suddenly realised that it won't let me swap them because of key violations.

I have three tables:
-operations
-schedule_order
-schedule_details

Schedule_order contains a list of keys tying back to the operations table, and that same key joins it all up to the details table.

The problem comes when I want to swap two of those keys, while keeping the "order" ID the same - the 1-1 relationship between the tables stops me from clearing or duplicating a key.

So what *is* the best way to do this? I could use an intermediate table but that seems needlessly complex - I could also just remove the 1-1 relationship with the details table and try to make sure there are never duplicate entries made, but that doesn't feel like a good idea!


How do you reckon I should do this? I might be able to do something using a UPDATE query with a IIF() in it perhaps?

Many thanks!

 

[arnelgp]

its not the PK of FK that you
need to manipulate. Create a New
Field on the table (Long Integer)
Fill it with ascending number. if
you like, use update query to update
the field like the PK (Autonumber).

Use this field to Swap the Record.
included is a sample. Look at MoveUpDown table,
the Swapping field is called Position.

Check the code behind "MoveUp" "MoveDown"
buttons on MoveUpDownMainForm.

 

[cprobertson1]

Perfect - worked like a charm Many thanks!