Follow up on the Text "ccon" field Logic

iif( NZ([tblMain].[CCon],False)=True,0,1) will be changed to:
 iif( nz([CCon],'No') = 'Yes' , 0, 1)
But I prefer to write function:
Function ccon_bool(CC as string) as boolean
ccon_bool = false
if isnull(CC) = false and ucase(CC) = "YES" then  ccon_bool = true
end function
and use this function in query:
 iif( NZ([tblMain].[CCon],False)=True,0,1) will be changed to:
 iif( cccon_bool([tblMain].[CCon]),0,1)

Sorry, typing error:
iif( ccon_bool([tblMain].[CCon]),0,1)

als315:
I did try your suggestion, however the logic has a sequence...but thanks for your assistance.

Mark:
The logic sequence now works fine in the query with the text type ccon...

 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,

....and.....

iif( NZ([tblMain].[CCon],"No")="Yes",-1,tblmain.rid),

Thanks.

Mark:
Quick question.

Will a union query provide complete calculation of the ccon logic without the "GetGroupIDNameCourseType" ?

- Just a wild guess.

Well, it depends if ccon means different handling of consolidated columns.

At one stage I seem to recall that ccon = "No" meant a different handling of how group and course were to be counted, but thought that might have extended into field consolidation as well.

But given the current query... We really want to only count the number of stype's if ccon = "Yes" and within the SQ you now use max() to get a count of 1 for the stypes otherwise, get a raw count. The maths seems to be fine and better than previous.

I am not sure what the routine/function does, but thought a consolidation of course was required and also a consolidation of group was required. It seems that group is no longer being consolidated.

Anyway, thats my simple thought process looking at the above given all the information I have been able to gather about your routines.

Does that help at all ?

Mark:
Thanks for the explanation. I will digest this info for now.

Mark:
Hope you dont mind this....
I am just curious....my original query was done about 1 year ago but I am since improved on the grouping style.

Kindly post a "slightly" modified query that aggregates each coursegroup by Math, Physics, Chemistry, Religion
..... using groupId type we've utilized throughout my previous posts  (as shown in the attached spreadsheet).

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",

I am just trying to check something out of curiosity ....that may generate my future EE post on the subject matter.

I will appreciate the extra effort.

Cheers
Bobby
Ccon-Aggregated-Sample.xls

Not exactly sure what you are asking for, depends a little on how you want the aggregation to work.

If ccon="Yes" for "boe" and ccon = "Yes" for "lt" then does that count as 1 or 2 ?  

Assuming the consolidation needs to work such that the count is now 2 for that condition, then you need to leave individual groupid in the subq, if it should count as 1 then you need to remove "tblmain.groupid as individual_groupid" from the select and remove "tblmain.groupid" from the group by within the subquery.

Also, not sure about "GetGroupIDNameCourseType" guessing that it combines Physics+Chemistry, so you might want to replace that with tblcourse.Course to get individual courses.

So guessing it is something like :

SELECT SUBQ.Cons_Group, SUBQ.CourseGroup, sum( SUBQ.CountOfRID ) AS CountOfRID, "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 ('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

      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 ('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:
This query works flawlessly.

I have decided to add a new EE question as a followup of this particular one at:
https://www.experts-exchange.com/questions/27047739/Follow-up-on-the-Text-ccon-field-Logic-Part2.html

Your assistance will be greatly appreciated.