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!
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"
However if a sample db is still required here for visualization, please request it and I will upload it.
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"
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;
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
iif( NZ([tblMain].[CCon],False)
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
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
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;
als315:
I did try your suggestion, however the logic has a sequence...but thanks for your assistance.
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")=
sum( iif( NZ([tblMain].[CCon],"No")=
....and.....
iif( NZ([tblMain].[CCon],"No")=
Thanks.
The logic sequence now works fine in the query with the text type ccon...
iif( NZ([tblMain].[CCon],"No")=
sum( iif( NZ([tblMain].[CCon],"No")=
....and.....
iif( NZ([tblMain].[CCon],"No")=
Thanks.
Mark:
Quick question.
Will a union query provide complete calculation of the ccon logic without the "GetGroupIDNameCourseType"
- Just a wild guess.
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 ?
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:
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','D
"BOE,LT,DDC,DEC,DEP,DOC,DO
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
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','D
"BOE,LT,DDC,DEC,DEP,DOC,DO
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"
So guessing it is something like :
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"
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;
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial