I have a form that is generated by a query. I want to press a batten and number a column from 1 to N sequentially. Say from top to bottom.
I guess the best way is to write a macro that does that. Does anyone knows how to do that?
Alternatively, one can run a SQL query to do that. I found this SQL for Access that does this:
WITH UpdateCTE
AS
(SELECT item_no,
ROW_NUMBER() OVER(ORDER BY item_no) AS new_value
FROM WO_Items)
UPDATE UpdateCTE
SET item_no = new_value;
Would this, or similar code, works in Base?
Let me explain my database that I am creating a little more to make it easier for people to understand it better.
I have built a database of tasks to be done. These Tasks are in Table “T_Task”
Table “T_Task” have following fields:
“ID” which is Primary Key
“Task” which is the task to be done
“Today_Order” which is the order to be done Today{ When a task is selected to be done today, it would get a decimal number <100}
“Status_Finished” which is status of the task
“Importance” which is importance of the task within its own category,
“Catagory” which is a foreign key pointing to Primary Key of Category table
Now the tasks that are selected to be done today will get a real number from 0 to 100 in their “Today_Order” field.
Then a form runs a Query that selects only the records that in their “Today_Order” is a number less than 100. This is the Query that does this:
SELECT "T_Task"."ID", "T_Task"."Task", "T_Task"."Today_Order", "T_Task"."Status_Finished", "T_Task"."Importance", "T_Task"."Catagory", "T_Catagory"."ID" "ID1", "T_Catagory"."Catagory" FROM { OJ "T_Catagory" RIGHT OUTER JOIN "T_Task" ON "T_Catagory"."ID" = "T_Task"."Catagory" }
WHERE "T_Task"."Today_Order" <= 100.00 ORDER BY "T_Task"."Today_Order" ASC
At this point I renumber the “Today_Order” of these records so I know which tasks to be done sooner or later.
If you have more than 20 tasks to be done in a day, you can not order all of them in one take. You order a few by giving their “Today_Order” a number, refresh the form and place other tasks in between these tasks by giving them a number between the already ordered tasks. As you proceed you get to situations where these numbers become to close together, and you need to go to many decimal points to place a task between them.
A this point I start renumbering the tasks which is a tedious chore if you have more than say 20 tasks. That is why I want to renumber them automatically.