I have different criteria for counting the number of assignments assigned to a course. So I use three different select statements. I want to display the results on a report. I had this working for counting the assignments for one course. But now I want to do it for all the courses and print off one big report. Here is one of the select statements.

select course.courseid, count(assignment.courseid) as qty;
from course left join assignment on course.courseid = assignment.courseid;
where assignment.courseid = thisform.fancyfield1.text1.value;
into cursor classi
qty = alltrim(str(classi.qty))

This worked for the one course:the one displayed on the form. Now when I get rid of the where statement, it doesn't count the assignments for each course. I only get one course with a total.

I then use another select statement to list the materials and textbooks.

select course.courseid, course.name, nvl(material.materialid, "None") as materialid,;
nvl(material.description, "None") as mname;
from course left join cmaterials on course.courseid = cmaterials.courseid;
left join material on cmaterials.materialid = material.materialid;
UNION select course.courseid, course.name, nvl(textbook.textid, "None") as materialid,;
nvl(textbook.textname, "None") as mname;
from course left join coursematerial on course.courseid = coursematerial.courseid;
left join textbook on coursematerial.textid = textbook.textid;
into cursor clmaterial

I then group the report by courseid and it lists each course and its texts and materials. The quantities are determined by the previous select statements and are displayed accordingly using a nested iif statement on my report.

iif(inlist(materialid,"VX29","VX39"), qty3, iif(inlist(materialid, "VX57","VX50"),qty2,iif(inlist(materialid, "VX51"),qty,iif(inlist(materialid,"CJ30","CJ31","CJ32","CJ33","CJ34","CJ35","CJ36","CJ37","CJ38","CJ39","CJ40"), "2","1"))))

I need to get the report to display the quantities for every course not just one course at a time like I used to do. How do I go about doing this?

Thanks in advance,
Tyler