query for students and their siblings

[sourabh20]

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

Hi

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

Student_ID(PK), Student_Name, Sibling1_Name, Sibling2_Name, Sibling3_Name
ROW1: 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.

 

[wazz]

http://support.microsoft.com/default.aspx?scid=kb;EN-US;209534

 

[MSAccessRookie]

First of all, that is probably not the best design, because the table is not normalized, and information will need to be unnecessarily repeated in the table. If that design is necessary, then I am sorry that I have no suggestions at this time.

---------------------------------------------------------

Another approach would be to make a database with two tables.

Table 1 is a list of students containing at least the following information (anything else you want/need to add is up to you)

Student ID (autonumber)
Student Name

Table 2 is a list of relationships from table #1

StudentID1
StudentID2

One form will be used to enter all of the necessary student information. A second from will be used to enter the relationships. A Combo Box could be used to select the two students that are related, and then all data would be entered only one time.

I hope this makes sense to you