Avatar of bobby6055
bobby6055
 asked on

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_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

Microsoft AccessMicrosoft ApplicationsSQL

Avatar of undefined
Last Comment
bobby6055

8/22/2022 - Mon
als315

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)
als315

Sorry, typing error:
iif( ccon_bool([tblMain].[CCon]),0,1)
ASKER CERTIFIED SOLUTION
Mark Wills

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
bobby6055

ASKER
als315:
I did try your suggestion, however the logic has a sequence...but thanks for your assistance.
Your help has saved me hundreds of hours of internet surfing.
fblack61
bobby6055

ASKER
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.
bobby6055

ASKER
Mark:
Quick question.

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.

Does that help at all ?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bobby6055

ASKER
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.

I will appreciate the extra effort.

Cheers
Bobby
Ccon-Aggregated-Sample.xls
Mark Wills

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
bobby6055

ASKER
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.