Follow up on the Text "ccon" field Logic

bobby6055
bobby6055 used Ask the Experts™
on
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_version
FROM (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_groupid

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],False)=True,-1,tblmain.rid), tblmain.groupid

) AS SUBQ
GROUP BY SUBQ.GroupID, SUBQ.CourseGroup, SUBQ.stype;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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)
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Think this is similar to your other questions...

SELECT SUBQ.GroupID, SUBQ.CourseGroup, SUBQ.SType, 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

      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

) AS SUBQ

GROUP BY SUBQ.GroupID, SUBQ.CourseGroup, SUBQ.stype;

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

Author

Commented:
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.

Author

Commented:
Mark:
Quick question.

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

- Just a wild guess.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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 ?

Author

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

Author

Commented:
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
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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;

Open in new window

Author

Commented:
Mark:
This query works flawlessly.

I have decided to add a new EE question as a followup of this particular one at:
http://www.experts-exchange.com/Microsoft/Applications/Q_27047739.html

Your assistance will be greatly appreciated.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial