Reporting: If statement and Sum statement

[Cozmo2]

I have an instructors schedule that shows each time a course is taught for example:

Course   Credits   Day

AAA123       3       Mon.

                             Wed.

The Credit only shows the first time (Print When Condition: calc.counter IS 1). I need to total the credits for each course when I use the Sum(credits) function it totals for each line (the above ezample would show a total of 6), I only want to sum if calc.counter EQ 1

[EddieLotter]

Please show your SQL query and also how you are calculating "calc.counter".

[Cozmo2]

My query looks like this:

<cfquery name="CFReportDataQuery" datasource="#session.ds#">

SELECT crssect."sch_yr", crssect."session", crssect."section", crssect."sp_taken", crssect."cancel", crssect."lead_fac",

crssect."sid", name."soc_sec", name."last_name", name."first_name", name."mi", name."suffix", course."course",

course."crs_txt", course."credits", course."crs_id", practicl."room_cod", practicl."start_hr", practicl."start_min",

practicl."start_ampm", practicl."end_hr", practicl."end_min", practicl."end_ampm", semester."sm_desc", sysvar."title",

dayweek."day_rid", dayweek."day_cod"

FROM

    { oj (((((((((("crssect" crssect INNER JOIN "course" course ON

        crssect."crs_id" = course."crs_id")

     INNER JOIN "rpt_sem" rpt_sem ON

        crssect."semester" = rpt_sem."semester")

     INNER JOIN "rpt_schy" rpt_schy ON

        crssect."sch_yr" = rpt_schy."sch_yr")

     INNER JOIN "name" name ON

        crssect."lead_fac" = name."soc_sec")

     INNER JOIN "semester" semester ON

        crssect."semester" = semester."semester")

     LEFT OUTER JOIN "practicl" practicl ON

        crssect."sid" = practicl."sid")

     INNER JOIN "sysvar" sysvar ON

        course."crs_id" <> sysvar."title")

     INNER JOIN "rpt_dept" rpt_dept ON

        name."dept_cod" = rpt_dept."dept_cod")

     INNER JOIN "rpt_div" rpt_div ON

        name."div_cod" = rpt_div."div_cod")

     INNER JOIN "rpt_camp" rpt_camp ON

        name."camp_cod" = rpt_camp."camp_cod")

     LEFT OUTER JOIN "dayweek" dayweek ON

        practicl."day_cod" = dayweek."day_cod"}

WHERE

    crssect."cancel" = 0 AND

    rpt_sem."token" = '#session.token#' AND

    rpt_schy."token" = '#session.token#' AND

    crssect."lead_fac" LIKE '#client.idnum#' AND

    rpt_div."token" = '#session.token#' AND

    rpt_dept."token" = '#session.token#' AND

    rpt_camp."token" = '#session.token#'

    UNION

SELECT crssect."sch_yr", crssect."session", crssect."section", crssect."sp_taken", crssect."cancel", crssect."lead_fac",

crssect."sid", name."soc_sec", name."last_name", name."first_name", name."mi", name."suffix", course."course",

course."crs_txt", course."credits", course."crs_id", practicl."room_cod", practicl."start_hr", practicl."start_min",

practicl."start_ampm", practicl."end_hr", practicl."end_min", practicl."end_ampm", semester."sm_desc",

sysvar."title", dayweek."day_rid", dayweek."day_cod"

FROM

    { oj (((((((((((("crssect" crssect INNER JOIN "course" course ON

        crssect."crs_id" = course."crs_id")

     INNER JOIN "rpt_sem" rpt_sem ON

        crssect."semester" = rpt_sem."semester")

     INNER JOIN "rpt_session" rpt_session ON

        crssect."session" = rpt_session."session_code")

     INNER JOIN "rpt_schy" rpt_schy ON

        crssect."sch_yr" = rpt_schy."sch_yr")

     INNER JOIN."practssn" practssn ON

        crssect."sid" = practssn."sid")

     INNER JOIN "name" name ON

        practssn."ssn" = name."soc_sec")

     INNER JOIN "semester" semester ON

        crssect."semester" = semester."semester")

     LEFT OUTER JOIN "practicl" practicl ON

        crssect."sid" = practicl."sid")

     INNER JOIN "sysvar" sysvar ON

        course."crs_id" <> sysvar."title")

     INNER JOIN "rpt_dept" rpt_dept ON

        name."dept_cod" = rpt_dept."dept_cod")

     INNER JOIN "rpt_div" rpt_div ON

        name."div_cod" = rpt_div."div_cod")

     INNER JOIN "rpt_camp" rpt_camp ON

        name."camp_cod" = rpt_camp."camp_cod")

     LEFT OUTER JOIN "dayweek" dayweek ON

        practicl."day_cod" = dayweek."day_cod"}

WHERE

    crssect."cancel" = 0 AND

    rpt_sem."token" = '#session.token#' AND

    rpt_session."token" = '#session.token#' AND

    rpt_schy."token" = '#session.token#' AND

    practssn."ssn" LIKE '#client.idnum#' AND

    rpt_div."token" = '#session.token#' AND

    rpt_dept."token" = '#session.token#' AND

    rpt_camp."token" = '#session.token#'

ORDER BY name."last_name", name."first_name", name."soc_sec", crssect."session",course."course", crssect."section", dayweek."day_rid", practicl."start_ampm", practicl."start_hr"

  </cfquery>

calc.counter - I count the number of course found and zero the counter when the soc_sec changes

[EddieLotter]

The quickest solution I can think of, off the top of my head, would be to have another calc field that stores the value of course.credits when course.course changes.

When course.course changes, you can use the new calc field to print the total and then reset the new calc field.

[Cozmo2]

Thanks for the suggestion. I ended up writing a report function.