i have a view named vw_admissionform with columns familycode,Regno,studentname,class for admission form . now if i want to print a student admission form by enetring registration no "regno" in txt1.text then his siblings should also be mentioned on the admission form as requirment by client.
for this am using familycode a unique code for students of same family .
i use select statment but how i can mention the siblings?

"select familycode,regno,studentname,class from vw_admissionform where regno= '"& txt1.text & "'"

Admission form format is

Student Name :----------------- Class:------------------

Registration No:------------------

Sibilings Name------------------- Class:---------------------

This should return the siblings:

"select b.studentname,b.class from 
vw_admissionform a inner join vw_admissionform b 
on a.familycode = b.familycode 
and a.regno = '" & txt1.text & "' 
and a.studentname <> b.studentname"

you can include in the select all the fields from your query - if you want them in the same record - like this :

"select a.familycode,a.regno,a.studentname,a.class, b.studentname,b.class from 
vw_admissionform a inner join vw_admissionform b 
on a.familycode = b.familycode 
and a.regno = '" & txt1.text & "' 
and a.studentname <> b.studentname"

Please note that I haven't tested the above queries and they may contain typos.

commented: WORKS PERFECTLY :) GOD BLESS YOU +0

Dear adam_k God Bless you :) Its working :)

BUT when am creating a view it gives me error "attept to store dublicate values it gives error "attept to store duplicate values"

CREATE VIEW VW_siblings (familycode, REGNO, STUDENTNAME, CLASS, SIBLINGS,CLASS)
AS    
SELECT  a.familycode,a.regno,a.stname,a.class, b.STUDENTNAME,b.class from
vw_admissionform a inner join vw_admissionform b
on a.familycode = b.familycode

and a.stname <> b.stname
;

Issue resolved i was repeating "class" name by changing it now its ok :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.