Follow up on the Text "ccon" field Logic
This question is a continuation of a related question.
tblMain.ccon is a text field of "Yes" and "No" TEXT. It's not a checkbox, it's in fact a Text field.
How can I "swap or re-create" this line .... "iif ( (sum( iif( nz([CCon],'No') = 'Yes' , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1))..."
...... into the Query in code snippet such that it changes the query from a "checkbox" type to a "Text" type "Yes and No" TEXT
such that the query will still output same result as if it's a checkbox. The query in code snippet is from one of my old EE posts
"GetGroupIDNameCourseType" is a Public Function in my db. If the above "Yes and No" logic is properly swapped or defined, the function and my query should work just fine.
However if a sample db is still required here for visualization, please request it and I will upload it.
SELECT SUBQ.GroupID, SUBQ.CourseGroup, SUBQ.SType, sum( SUBQ.CountOfRID ) AS CountOfRID, "V004C" AS query_versionFROM (SELECT GetGroupIDNameCourseType(tblcourse.Course,tblmain.stypeid) AS GroupID,[Course] AS CourseGroup, tblSType.SType, iif( NZ([tblMain].[CCon],False)=True,-1,tblmain.rid) as ccongroup, sum( iif( NZ([tblMain].[CCon],False)=True,0,1)) + max( iif( NZ([tblMain].[CCon],False)=True,1,0)) AS CountOfRID, tblmain.groupid as individual_groupidFROM ((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],False)=True,-1,tblmain.rid), tblmain.groupid) AS SUBQGROUP BY SUBQ.GroupID, SUBQ.CourseGroup, SUBQ.stype;
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)
Will a union query provide complete calculation of the ccon logic without the "GetGroupIDNameCourseType" ?
- Just a wild guess.
Mark Wills
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.
Mark:
Thanks for the explanation. I will digest this info for now.
bobby6055
ASKER
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.
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_versionFROM (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 SUBQGROUP BY SUBQ.Cons_Group, SUBQ.CourseGroup;
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)
iif( cccon_bool([tblMain].[CCon