troubleshooting Question

Follow up on the Text "ccon" field Logic

Avatar of bobby6055
bobby6055 asked on
Microsoft AccessMicrosoft ApplicationsSQL
11 Comments1 Solution505 ViewsLast Modified:
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

ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros