BEGINNER:Already normalised it now.Guide me to write update query.Urgent help needed.

[sourabh20]

Hi

I have a table with Columns: Student_ID,Student_Name,Sibling1_name,Sibling2_nam e,Sibling3_name.

Student_ID(PK), Student_Name, Sibling1_Name, Sibling2_Name, Sibling3_NameROW1: 1, A, B, C, D ROW2: 2, E, F, G
ROW3: 3, H, I
ROW4: 4, J







For every Student_ID I have 1 unique student name and his/her siblings.

I want to create a query:

1. To copy all these siblings to Student_Name column.


S.No.(Autonumber), Student_ID(PK) Sibling1_Name Sibling2_Name Sibling3_Name ROW1: 1, 1, A, B, C, D
ROW2: 1, 2, E, F, G
ROW3: 3, 3, H, I
ROW4: 4, 4, J
ROW5: 5, 1, B
ROW6: 6, 2, F
ROW7: 7, 3, I
ROW8: 8, 1, C
ROW9: 9, 2,G
ROW10:10, 1, D

then

2. To copy the names of all the siblings(including the original student) realted to the new student(Ist column) in front of the new student.

Like this:


S.No., Student_ID(PK) Student_Name Sibling1_Name Sibling2_Name Sibling3_Name ROW1: 1, 1, A, B, C, D
ROW2: 2, 2, E, F, G
ROW3: 3, 3, H, I
ROW4: 4, 4, J
ROW5: 5, 1, B ,A, C, D
ROW6: 6, 2, F, E, G
ROW7: 7, 3, I, H
ROW8: 8, 1, C, A , B, D
ROW9: 9, 2, G, E, F
ROW10: 10, 1, D, A, B, C



Please guide me for this query or procedure.



Thanks,

S.B.

 

[boblarson]

First of all despite your claim that this is normalized, it is not. It is so easy to tell that it isn't when you have repeating fields (Sibling1, Sibling2, etc). This is a one-to-many situation and therefore requires at least one, if not two other tables.