hi all,
i am facing some problems with my database.i have a table with users info, in the same table there is a username and password,initially for each user there would be only one username and pass(account).But now i a have allow multiple accounts for the same person, which means i have to split the table into three:
 userstable:
userid fname lname (other info)
accounttable:
usename passwrd (other account info)
usersaccounttable:
userid username
 the problem is that the table is already populated with data, and the columns have all the same data type,is there a way to split the data into these three tables without loosing anything?
Thanks.

Why three tables? From what understand of your situation, you can do this with two tables.

Table1: User
userid(pk) fname lname (other info)

Table2: UserAccount
useraccountid(pk) userid (fk) usename passwrd (other account info)

Then, update the UserAccount:
INSERT INTO UserAccount (userid, username, passwrd) SELECT userid, username, passwrd FROM User

Scott

Let me add a little more.

By doing what you suggested below, the UserAccount table now has the ability to store multiple accounts for each User. This is accomplished via the Primary / Foreign key reference between the User and UserAccount tables.

I hope this helps...

Scott

ok thanks for the tip but i gave only an example, here is the real problem:
table1
userid(pk) (otherinfo)
table2
useraccount(pk) userid(fk) username password status

the status is the same for all accounts of the user, and i made a mistake not to include it in the user table1, because now i have redundancy in table 2, however i solved the problem now, but i need a safe and simple way to avoid such situations in the future(other than carefully designing the DB hehehe)
thanks anyway, hope you got my point of view.