AboutCarlson Lim Expertise I can answer questions about database design for OLTP and OLAP. I can can answer questions about logical models and physical star schema models. I can answer questions on ERwin.
Experience Experience in the area I currently work on database design on a day to day basis. I have years of experience with Oracle, SQL Server, and database design tools.
Organizations DAMA
Education/Credentials BS Computer Engineering
Multiple Data Warehouse Class Completion Certificates
Expert: Carlson Lim Date: 5/5/2007 Subject: ms access
Question Hi expert, I have an access database and I'm stuck with how to rank records. What i did was place a rank field (text box ) on the form and i set it to no duplicates. That way, the user cannot enter the same number. I was also able to trap errors.
I think i'm having problem where, each record when i rank. I want to be able to do it so for example
I rank task 1 as rank 3,
task 2 as rank 4
task 3 as rank 1
task 4 as rank 2
task 5 as rank 5
and If the user decides to go in and change task 4 to rank 3, it should be smart enough to automatically rank / push the other ranks to the next number. Do you get me?
thus:
I rank task 1 as rank 4,
task 2 as rank 5
task 3 as rank 1
task 4 as rank 3
task 5 as rank 6
Would you be able to assist me in how to code this? Thanks so much.
Answer Let's say the table for this is named Task, with columns TaskID, TaskName and RankNumber.
I'm not sure how to implement this in MS Access forms, but it is pretty simple to do in SQL Server or Oracle.
Once a record in table Task is updated, an increment has to occur where the rank is equal to or greater than the record just updated:
update N
set RankNumber = RankNumber + 1
from Task U -- user updated task rank
inner join Task N -- auto updated ranks
where N.TaskID <> U.TaskID
and U.RankNumber >= N.RankNumber
The above code can either be altered to be put into a stored procedure and called when a rank is updated or used in a trigger to be fired when a record is updated.
