Follow up on the Text "ccon" field Logic (Part2)

well, not sure what type of column 'courserepeated' is - maybe we need to test or check or sum. For now, I have assumed it is a number field and we simply sum() it

you might need to comment it out to test the other code if it causes any problems.

SELECT SUBQ.Cons_Group, SUBQ.CourseGroup, sum( SUBQ.CountOfRID ) AS CountOfRID, sum(countrepeats) as countcourserepeated, "V004C" AS query_version

FROM (SELECT     GetGroupIDNameCourseType(tblcourse.Course,tblmain.stypeid) AS GroupID,[Course] AS CourseGroup,  tblSType.SType,   
                 iif( NZ([tblMain].[CCon],"No")="Yes",-1,tblmain.rid) as ccongroup, 
                 sum( iif( NZ([tblMain].[CCon],"No")="Yes",0,1)) + max( iif( NZ([tblMain].[CCon],"No")="Yes",1,0))  AS CountOfRID, 
                 tblmain.groupid as individual_groupid,
                 IIf(tblMain.GroupID In ('CBS','MHL','SPB'),"CBS,MHL,SPB", 
                     iif (tblMain.GroupID In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR'),
                         "BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR", tblmain.groupid)) as cons_group,
                 sum(CourseRepeated) as countrepeats

      FROM     ((tblMain INNER JOIN tblCourse ON tblMain.CourseID = tblCourse.CourseID) 
      inner join tblSType on tblSType.STypeID = tblmain.STypeid)

      WHERE      tblMain.Appdate >=[forms]![frmReportDateRange]![BeginDate] And tblMain.Appdate<=[forms]![frmReportDateRange]![EndDate]

      GROUP BY   GetGroupIDNameCourseType(tblcourse.Course,tblmain.stypeid),[Course],  tblSType.SType,   
                 iif( NZ([tblMain].[CCon],"No")="Yes",-1,tblmain.rid), 
                 tblmain.groupid,
                 IIf(tblMain.GroupID In ('CBS','MHL','SPB'),"CBS,MHL,SPB", 
                     iif (tblMain.GroupID In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR'),
                         "BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR", tblmain.groupid))

) AS SUBQ

GROUP BY SUBQ.Cons_Group, SUBQ.CourseGroup;

Select allOpen in new window

Mark:
I added a field in tblMain and named it [CourseRepeated].
I created a query to input "1" in cell CourseRepeated for all the records in tblMain then ran your above query.

.... I received an error....
You tried to execute a query that does not include the specified expression 'Cons_Group' as part of an aggregate function.

SELECT SUBQ.Cons_Group, SUBQ.CourseGroup, sum( SUBQ.CountOfRID ) AS CountOfRID, sum(CourseRepeated) AS countcourserepeated, "V004C" AS query_version
FROM (SELECT     GetGroupIDNameCourseType(tblcourse.Course,tblmain.stypeid) AS GroupID,[Course] AS CourseGroup,  tblSType.SType,
                 iif( NZ([tblMain].[CCon],"No")="Yes",-1,tblmain.rid) as ccongroup,
                 sum( iif( NZ([tblMain].[CCon],"No")="Yes",0,1)) + max( iif( NZ([tblMain].[CCon],"No")="Yes",1,0))  AS CountOfRID,
                 tblmain.groupid as individual_groupid,
                 IIf(tblMain.GroupID In ('CBS','MHL','SPB'),"CBS,MHL,SPB",
                     iif (tblMain.GroupID In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR'),
                         "BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR", tblmain.groupid)) as cons_group,
                 sum(CourseRepeated) As countrepeats

      FROM     ((tblMain INNER JOIN tblCourse ON tblMain.CourseID = tblCourse.CourseID)
      inner join tblSType on tblSType.STypeID = tblmain.STypeid)

      WHERE tblMain.Appdate >= [Forms]![frmReportDateRange]![BeginDate] And tblMain.Appdate <= [Forms]![frmReportDateRange]![EndDate]

      GROUP BY   GetGroupIDNameCourseType(tblcourse.Course,tblmain.stypeid),[Course],  tblSType.SType,
                 iif( NZ([tblMain].[CCon],"No")="Yes",-1,tblmain.rid),
                 tblmain.groupid,
                 IIf(tblMain.GroupID In ('CBS','MHL','SPB'),"CBS,MHL,SPB",
                     iif (tblMain.GroupID In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR'),
                         "BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR", tblmain.groupid))

) AS SUBQ;

Select allOpen in new window

Oops missed the very last line in the copy and paste...

after line 23 (ie after ") AS SUBQ" ) kill the semicolon after SUBQ and add the line:

GROUP BY SUBQ.Cons_Group, SUBQ.CourseGroup


think thats what it is complaining about :)

Mark:
After running the amended query, I received an "Enter Parameter Value dialog" for CourseRepeated.

that must be because it cannot find it in the database.

Mark:
Two problems are coming up with the query:

(a). The "Enter Parameter Dialog Box"  Question does not seems to be a db problem. It is somthing else I cant pinpoint.
(b). The output count of the "CountOfRid" is incorrect for all the group because, it's counting group stypeid = "SA" as a count of 1
       WHERE ccon="Yes" and also counting group stypeid = "CM" as another count of 1 WHERE ccon="Yes", the query then adds
       the count of SA and count of CM together. (see the attached spreadsheet for visualization and explanation)

      The correct count should be count all Coursegroup WHERE ccon = Yes as "1" (irrespective of the STYPEID)
      then add this count to the Coursegroup WHERE ccon = No (if any)

Please download the attched sample for illusteation and test using 07/01/09 and 07/31/09 as Range dates.
Ccon-Sample-data.mdb
CourseGroup-Count.xls

OK,

a) oops, rename of column using an alias... s/b sum(countrepeats) on the top line
b) is part of that aggregation question I was asking above. S/B easy to fix.

The spreadsheet and the database dont add up as far as I can tell. Using the RID to identify single rows, then they are different from the spreadsheet to the database.

Will play with the database.

I used my old spreadsheet for the same question - I did not recreate it.

If you require a new spreadsheet, please let me know. I'll upload a new one.

Mark:
Any luck with the correction?

Sorry bobby, will be getting back into it later today (my time). I think we are pretty close, was reconciling the results from the actual / sample database provided above.

Mark:
I will appreciate your assistance in resolving the problem.

Mark:
I will appreciate your last minute solution for this post so I can close it.

OK, just in the middle of something, wont be long...

reckon this is it...

SELECT     GetGroupIDNameCourseType(tblcourse.Course,tblmain.stypeid) AS GroupID,[Course] AS CourseGroup, 
                 IIf(tblMain.GroupID In ('CBS','MHL','SPB'),"CBS,MHL,SPB",
                     iif (tblMain.GroupID In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR'),
                         "BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR", tblmain.groupid)) as cons_group,
              sum( iif( NZ([tblMain].[CCon],"No")="Yes",0,1)) +
              max( iif( NZ([tblMain].[CCon],"No")="Yes",1,0)) AS CountOfRID,
              sum(CourseRepeated) As countrepeats

      FROM     ((tblMain INNER JOIN tblCourse ON tblMain.CourseID = tblCourse.CourseID)
      inner join tblSType on tblSType.STypeID = tblmain.STypeid)

      WHERE tblMain.Appdate >= [Forms]![frmReportDateRange]![BeginDate] And tblMain.Appdate <= [Forms]![frmReportDateRange]![EndDate]

      Group BY   GetGroupIDNameCourseType(tblcourse.Course,tblmain.stypeid),[Course],
                 IIf(tblMain.GroupID In ('CBS','MHL','SPB'),"CBS,MHL,SPB",
                     iif (tblMain.GroupID In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR'),
                         "BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR", tblmain.groupid))

Select allOpen in new window

Mark:
We are almost there. Some fields are needed to be group together under the GroupID as shown in my latest spreadsheet sample (see attached)
Ccon-Latest-Output-01a.xls

Well, to achieve that, you need to change the module "GetGroupIDNameCourseType(tblcourse.Course,tblmain.stypeid)"

because it is seperating out the GroupID's

Is that what you are looking for ?

Yes, Please

Mark:
Your last posted looks good, in order to compare the output result, We almost done.

(a). I will appreciate further breakdown query that inludes the STYPES ("ONCampus", "OffCampus" and "Online")

(b). Kindly add the capability to be able to count the number of "YES" and the number of "NOS' for the each category

.........I tried it myself but had all kinds of errors.

sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1)) AS CountOfcconNO,
sum ( iif( nz([CCon],'No') = 'Yes' , 1, 0)) AS CountOfcconYES,

  iif ( (Sum(IIf(NZ(tblMain.CCon,"No")="Yes",0,1))+Max(IIf(NZ(tblMain.CCon,"No")="Yes",1,0) AS CountOfRID, Sum(tblMain.Courserepeated))) AS countrepeats

Mark:
Superb !!!
I have gone through all the threads you've assisted me so far and compare left, right and center. Your last post so far proved your ability to tweak any complex SQL, recompile and reach logical solutions WHERE others run away.

You have always said you love challenges and you definitely proved it again, again and again. I am impressed and appreciated your time inputs despite your busy schedule.

Superb !!!