Comparing recordsets 2

[karuri73088]

I have two recordsets in which rs and rs1. In rs I have results of candidates courseid, mark, date etc. in rs1 I have courses with their passmarks. I would like to filter in a listview between two dates then compare the mark in rs with the passmark in rs1. Any hints??? I have the following code but it only compares all the marks in rs with only the first passmark in rs1. Your help will be appreciated. Part of my code is as follows. Thanks

If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst

With rs

.Filter = &quot;ResultDate <> null &quot;

Do While Not .EOF

rs1.MoveFirst

If !courseid = rs1.Fields!courseid Then
k = rs1.Fields!PassMark
Else
rs1.MoveNext
End If

If !Mark >= k Then
If !ResultDate >= h Then
If !ResultDate <= i Then

Set strlistItem = ListView.ListItems.Add(, , rs!CandidateID)
strlistItem.SubItems(1) = !ModuleID
strlistItem.SubItems(2) = !courseid
strlistItem.SubItems(3) = !ResultDate
strlistItem.SubItems(4) = !Mark

End If
End If
End If
.MoveNext
Loop

.Filter = adFilterNone

End With
End If

End Sub

 

[jebenson]

Let me see if I have this correct:

You have a recordset (rs1) with all of the courses, with the grade required to pass the course (passmark). In another recordset (rs) you have all of the students, what courses they took, when they too them, and the grades they made (marks). You want to compare the student's mark in a particular course with the passmark for that course.

Assuming that this is correct, here is how I would do it:

rs1.MoveFirst
For r = 0 To rs1.RecordCount - 1
rs.Close
rs.Open &quot;select * from <candidates table name here> WHERE courseid=&quot; & rs1!courseid&quot;

If rs.RecordCount > 0 Then
rs.MoveFirst
For c = 0 To rs.RecordCount - 1
If rs!ResultDate >= h And rs!ResultDate <= i Then
If rs!mark >= rs1!passmark Then
'Do processing for passing mark
Else
'Do processing for failing mark
EndIf
EndIf
rs.MoveNext
Next c
EndIf
rs1.MoveNext
Next r

First, you go through every course in the rs1 recordset, and select all students who took that course into rs. If no students took that course (rs.RecordCount = 0), then you go to the next course. If students did take the course, check that dates for each, and if the date falls within the range, check their marks against the passmark.

I hopr this helps. Good luck.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[karuri73088]

Thanks Jebenson, your post was very helpful. Hope you can get some time to rock and roll.

 

[MichaelRed]

but WHY the code? Doesnt a SINGLE SELECT Query do the job? The two recordsets joined on the course and paramerterized to between the dates should return all the students who took any course between the specified dates. Since the &quot;comparision of the 'mark' isn't specified, I assume it is just the traditional fail, which is a simple pass / fail criteria where &quot;Pass&quot; is Mark >= PassMark, expressed in the simple (if traditional) IIF syntax (alternate return being &quot;Fail&quot;.

So, what am I missing that it requires multiple recordsets and looping through one whilst looking up in the other?????

What is so complicated about this? Aren't 'we' being a bit less than &quot;professional&quot; in resorting to CODE when a SIMPLE select query should work?




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[daveod]

Thanks Michael

I'm developing an application for a mailing list which is generated on the fly from a selection of teams and need to eliminate duplicate addresses where husband and/or wife may or may not be present in the list.

Your blast was just what I needed to get me out of the recordset swamp!

Star on the way.

Regards
Dave