multiple commands in APPEND

[dodimanal]

hi friends
i want to use multiple queries an APPEND clause. I used the following code but it gives syntax error. plz help me as soon as possible.


rs.Open "Shape {SELECT DISTINCT sem From qAwd WHERE seat_no=" & stno & " AND class= '" & deg & "' ORDER BY sem} As Parent Append (({Select crs_title, crs_no, cr_hrs, lab, midterm, terminal, total_marks, total, crs_index, remarks, sem, ayr From qAwd WHERE seat_no=" & stno & " ORDER BY sem, crs_no, exm_date} Relate sem To sem) As com1) Append (({Select total, sem From qAwd WHERE seat_no=" & stno & " AND remarks='" & passStudetn & "' ORDER BY sem} Relate sem To sem) As com2)", con, adOpenStatic, adLockReadOnly, adCmdText

[brucevde]

Do you want a Parent Child, Child relation or Parent, Child, GrandChild relation?

I assumed Parent Child, Child.

For that you only need 1 Append clause, with each Select clause separated by a ,. Also, the alias name of the Chapter must appear before the Relate clause.

rs.Open "Shape {SELECT DISTINCT sem From qAwd WHERE seat_no=" & stno & " AND class= '" & deg & "' ORDER BY sem} As Parent " _
& "Append ({Select crs_title, crs_no, cr_hrs, lab, midterm, terminal, total_marks, total, crs_index, remarks, sem, ayr From qAwd WHERE seat_no=" & stno & " ORDER BY sem, crs_no, exm_date} As Com1 Relate sem To sem), " _ 
& "({Select total, sem From qAwd WHERE seat_no=" & stno & " AND remarks='" & passStudetn & "' ORDER BY sem} As Com2 Relate sem To sem) ", con, adOpenStatic, adLockReadOnly, adCmdText

[dodimanal]

thanks a lot , this query solves my prob..but now i have another problem i.e. when i try to display any field e.g. (semTotal) of com2 in a textBox it gives error " Data Field semTotal[Com2] not found", Actually i want to show this field in the DataReport's Group Footer. the code i used is:

rs.Open "Shape {SELECT DISTINCT sem From qAwd WHERE seat_no=" & stno & " AND class= '" & deg & "' ORDER BY sem} As Parent " _
& "Append ({Select crs_title, crs_no, cr_hrs, lab, midterm, terminal, total_marks, total, crs_index, remarks, sem, ayr From qAwd WHERE seat_no=" & stno & " ORDER BY sem, crs_no, exm_date} As Com1 Relate sem To sem), " _
& "({Select SUM(total) As semTotal, sem From qAwd WHERE seat_no=" & stno & " AND remarks='" & passStudetn & "' GROUP BY sem ORDER BY sem} As Com2 Relate sem To sem) ", con, adOpenStatic, adLockReadOnly, adCmdText
Set dResult.DataSource = rs
dResult.Show
 
With dResult.Sections("Section7")
.Controls("txtSemTotal").DataField = semTotal
.Controls("txtSemTotal").DataMember = Com2
 
End With

[dodimanal]

I think the reason of this error is that sometimes no record exist for Com2 while there is a number of records for Com1. Actually Com1 returns the marks obtained by a student in different subjects of each semester Whether the student passed that course or failed. in the other hand, Com 2 returns the marks of only that courses that are passed by the student as i wanna calculate the sum of passed courses and display it in the group footer, but i also want to show the marks of all courses of a semester in the group detail section. For some semesters the student is fail in all the courses i.e. no record exist for Com2. Now plz tell me how can i deal with this prob. how can i write a code to show the semTotal field if there are records for Com2 and not to show it if no record exist for Com2. plz help me