Click here to Skip to main content
15,919,245 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am creating a query in MS Access that needs to update newly created field with the total number of previous records. Something like this:

Original table:
SQL
|  ID | Name |
--------------
|  1  | Mark |
--------------
|  2  | Mich |
--------------
|  3  | Isak |
--------------


Now I add another column ( of type int ), e.g. Ordinal_Number :

SQL
|  ID | Name | Ordinal_Number |
-------------------------------
|  1  | Mark |                |
-------------------------------
|  2  | Mich |                | 
-------------------------------
|  3  | Isak |                | 
-------------------------------


Now I need to fill Ordinal_Number with the number of previous records :

SQL
|  ID | Name | Ordinal_Number |
-------------------------------
|  1  | Mark |       0        |
-------------------------------
|  2  | Mich |       1        | 
-------------------------------
|  3  | Isac |       2        | 
-------------------------------


I couldn't even get the idea how to try fighting this problem so I am asking here.
Posted
Comments
thatraja 5-May-14 3:38am    
/* fill Ordinal_Number with the number of previous records */
Not clear. Do you want to insert the previous ID values in Ordinal_Number column? Like Ordinal_Number = 1 when ID = 2
AlwaysLearningNewStuff 5-May-14 14:58pm    
* fill Ordinal_Number with the number of previous records */

Everything is crystal clear : I want to insert the number of previous records in that field, like Ordinal_Number = COUNT(*) FROM MyTable but it should count the number of current record's predecessors. Something like Ordinal_Number = COUNT(*) FROM MyTable WHERE Ordinal_Number <= current_records_Ordinal_Number_value;

1 solution

When you are inserting data in table, before inserting find maximum number of ID and then you can pass that ID value - 1, with name to save in table
 
Share this answer
 
Comments
AlwaysLearningNewStuff 5-May-14 2:39am    
I need to add a new integer field into existing database. Therefore I need to update that field immediately after I add it.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900