Click here to Skip to main content
15,439,117 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

I want to update series number(not the primary key into a temporary table using Ms-access query.

Every time i deleted the record from that table and insert new rows.

Ex:--

Table

EmpName Desig     Loc
AA      Officer   Hyd
Bb      Mgr       Ind
CC      Assoc     Us
DD      Sr.Mgr    UK


I want the table with series number

S.No  EmpName Desig     Loc
 1     AA      Officer   Hyd
 2     Bb      Mgr       Ind
 3     CC      Assoc     Us
 4     DD      Sr.Mgr    UK


Like this


Thanks in advance
Sucharitha
Posted
Updated 2-Jan-13 1:50am
v3
Comments
Zoltán Zörgő 2-Jan-13 7:41am    
How did you imagine the "every time i deleted the record" part?

1 solution

create column srno with datatype int
now, while adding record in database
SQL
Insert into tbl
select
(select Isnull(max(srno),0)+1 from tbl) as srno,
'abc' as EmpName ,
'Officer' as Desig,
'Ind' as Loc

Happy Coding!
:)
 
Share this answer
 

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