Data Report - Data Environment Command SQL

[vb6Access]

I am trying to run a data report in Visual Basic. I have set up a command in the data environment that will filter a query to show the results I want. The query runs fine, but I cannot get the report to limit the number of rows per student to one. I just want to run the most recent test date and scores for each student. The existing SQL code that I have in there now is as follows:

select studentid, firstname, mi, lastname, testdate, testtype, testtitle, score, `testsubject`, `programtype1`, `programtype2`, `sitewish1`, `sitewish2` from qry_rpt_studentregistered1 where (((`sitewish2` = ?) or (`sitewish1` = ?)) and ((`programtype2` = ?) or (`programtype1` = ?)) and ((`testsubject` = ?)))

The query contains 3 joined tables. The way I want the report to look something like below:

Name TestDate TestType TestTitle Score
(One Record per student)

If you have any suggestions I thank you in advance.

 

[mdthornton]

You'll need to write another query to return the max test date for each student then join this into your existing query (qry_rpt_studentregistered1) or in the SQL above to limit the rows returned, eg:

qMaxDates:

select sutdentID, max(testDate)
from myData
group by studentID Best Regards,
Mike

 

[rajeessh]

Try this now

select studentid, firstname, mi, lastname, testdate, testtype, testtitle, score, `testsubject`, `programtype1`, `programtype2`, `sitewish1`, `sitewish2` from qry_rpt_studentregistered1 as x where (((`sitewish2` = ?) or (`sitewish1` = ?)) and ((`programtype2` = ?) or (`programtype1` = ?)) and ((`testsubject` = ?)))
where
1 = (Select count(*) from qry_rpt_studentregistered1 as y where y.studentid = x.studentid)

 

[vb6Access]

Thanks for your help. I fixed the problem by making 3 queries (in ms access) and then running the parameter code (listed in the top entry above) in a command in the dataenvironment.

I should have thought of this before. Using the Max function in the 2nd query really did the trick. I actually thought of this before, but I did not relate the 2 queries (in the 3rd query) together using the StudentID and the MaxDate (these 2 together make up the primary key). THANKS AGAIN!