Tyler,

You can't just assign the COUNT result to a variable, since you have multiple courseid's to report on, so forget the "qty = alltrim(str(classi.qty))" line.

You should do the first two SELECTs into tables instead of cursors, then index the tables on courseid.

Then, after the last SELECT, relate it to the first two tables with

SET RELATION TO courseid INTO FirstTempTableName
SET RELATION TO courseid INTO SecondTempTableName ADDITIVE

Lose the third SELECT, since you can calculate those values from #1 minus #2.

In your report when you need the qty, qty2, etc., refer to them by alias.field, as in "FirstTempTableName.qty", "SecondTempTableName.qty2", etc. Qty3 would be "FirstTempTableName.qty - SecondTempTableName.qty2" as the expression on the report.

You can alternatively do it all in a final SELECT that combines # 1, 2, and 4 instead of the SET RELATION business, but I can't spend any more time on this at the moment.

>Thanks alot David. That got all of my quantities correct. Now when I print the report how do I get the quantity to show up for the correct course? Here is my new code if that will help.
>
>select course.courseid, count(assignment.courseid) as qty;
>from course left join assignment on course.courseid = assignment.courseid;
>into cursor classi;
>group by course.courseid
>qty = alltrim(str(classi.qty))
>
>
>select course.courseid, count(assignment.courseid) as qty2;
>from course left join assignment on course.courseid = assignment.courseid;
>where (assignment.description LIKE '%TEST%';
> OR assignment.description LIKE '%QUIZ%';
> OR assignment.description LIKE '%EXAM%';
> OR assignment.description LIKE '%MIDTERM%');
>into cursor ccrequest;
>group by course.courseid
>
>
>select course.courseid, count(assignment.courseid) as qty3;
>from course left join assignment on course.courseid = assignment.courseid;
>where assignment.description NOT LIKE '%TEST%';
> AND assignment.description NOT LIKE '%QUIZ%';
> AND assignment.description NOT LIKE '%EXAM%';
> AND assignment.description NOT LIKE '%MIDTERM%';
>into cursor whatever;
>group by course.courseid
>qty3 = alltrim(str(whatever.qty3))
>
>
>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
>
>
>The nested iif statement is the same. I group the report by courseid from the clmaterial cursor.
>
>Tyler