Create Priority list

[mafhobb]

Hello.

I have been asked if I can add a priority list to my database and I am trying to figure out how to do it.

The database already exists and this would be added code/forms. Basically this database has a number of users, all identified by a login name. Each of these users have several projects assigned to them. The projects are identified by a unique project number.

What I need to do is to be able to list all the projects that a user has, which is easy to do by simply creating a query, but then the user needs to be able to type in a priority number (1 to infinity) for each of these projects with no number repetitions. If a project priority is updated, say from 6 to 2, then the projects that had priorities 2 to 5, get a +1 added to their priority number so they go 3 to 6. Likewise if a project gets "demoted" (priority is lowered) then the ones below go up one.

Any ideas on what's the best way to accomplish this? Suggestions? Strategies?

Thanks

mafhobb

 

[Beetle]

You would need a field in the table to store the priority value, then an update query or two to reset the values after a priority change. You wouldn't be able to set the table field to No Duplicates because duplicates would temporarily be created during the update process, so the No Duplicates rule would need to be handled at the form level.