bobby6055
asked on
Test for a TEXT type field in a query
There is a related question for this post.
I recently obtained a query (in the code snippet) that works fine. I will like to add a test for tblMain.ccon field - a 3 digit character
which will restrict the type of records that should be pulled. In other words
agregate records based on ccon logic.
In other words, I will like to add the line below back into the query in the code snippet to function as it was originally designed.
iif ( (sum( iif( nz([CCon],'No') = 'Yes' , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1)) AS CountOfCourseID,
In addition, I want to be able to modify the query further so that I will have the flexibility to remove or add items from tblMain.STypeID to be used as a filter in pulling data from tblMain
by placing something like....
AND tblMain.STYpeID In ('CM','SA'),
in my query. This will allow me pull specific records at will
I recently obtained a query (in the code snippet) that works fine. I will like to add a test for tblMain.ccon field - a 3 digit character
which will restrict the type of records that should be pulled. In other words
agregate records based on ccon logic.
In other words, I will like to add the line below back into the query in the code snippet to function as it was originally designed.
iif ( (sum( iif( nz([CCon],'No') = 'Yes' , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1)) AS CountOfCourseID,
In addition, I want to be able to modify the query further so that I will have the flexibility to remove or add items from tblMain.STypeID to be used as a filter in pulling data from tblMain
by placing something like....
AND tblMain.STYpeID In ('CM','SA'),
in my query. This will allow me pull specific records at will
SELECT tblCourse.Course,
IIf(tblMain.CourseID in ("PL","PHY","CHM") And 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",
IIf(tblMain.CourseID="NEW" And tblMain.GroupID In ('CBS','MHL','SPB'),
"CBS,MHL,SPB",
IIf(tblMain.CourseID In ("MTH","LA","RC") And 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 GRP,
tblStype.stype,
count(*) AS countofcourseid
FROM ((tblMain
INNER JOIN tblCourse ON tblMain.CourseID=tblCourse.CourseID)
INNER JOIN tblStype ON tblMain.StypeID=tblStype.StypeID)
WHERE (tblMain.Appdate)>=forms!frmReportDateRange!BeginDate And (tblMain.Appdate)<=forms!frmReportDateRange!EndDate
GROUP BY tblCourse.Course,
IIf(tblMain.CourseID in ("PL","PHY","CHM") And 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",
IIf(tblMain.CourseID="NEW" And tblMain.GroupID In ('CBS','MHL','SPB'),
"CBS,MHL,SPB",
IIf(tblMain.CourseID In ("MTH","LA","RC") And 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))),
tblstype.stype
Can I ask why CCon is a string field instead of a Boolean (True/False) field? You are using a lot of code to convert 'Yes/No' to 1/0. If CCon were Boolean:
-Sum(CCon) as CountCCon
In an Access table a Boolean field is either True (-1) or False (0) - it can never be Null.
-Sum(CCon) as CountCCon
In an Access table a Boolean field is either True (-1) or False (0) - it can never be Null.
ASKER
GRAYL,
You asked: "why CCon is a string field instead of a Boolean (True/False) field?"
It was an office policy designed to control certain user habits. I have raised that question myself before but this is how they want it for now.
You asked: "why CCon is a string field instead of a Boolean (True/False) field?"
It was an office policy designed to control certain user habits. I have raised that question myself before but this is how they want it for now.
It looks like the 'office policy' is encouraging bad design habits. I'm glad I don't work there ;-)
ASKER
Mark:
I will like to experiment your suggested idea under ID:35719850.
Cheers
Bobby
I will like to experiment your suggested idea under ID:35719850.
Cheers
Bobby
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Mark:
Your query result is good, however, "Physics and Chemistry" have separate counts listed in separate cells....which is still okay
....but.....
kindly post the same type query where "Physics and Chemistry" are combined into one cell as Course and also
sum the total count for both the "PHY" and "CHM" together into one cell such that it looks like the one below
Course Grp Stype CountofRows Countof...........
Math BOE,LT,DDC,DEC,DEP,DOC,DOH
Math BOE,LT,DDC,DEC,DEP,DOC,DOH
......etc....
Physics,Chemistry BOE,LT,DDC,DEC,DEP,DOC,DOH
Physics,Chemistry BOE,LT,DDC,DEC,DEP,DOC,DOH
Your query result is good, however, "Physics and Chemistry" have separate counts listed in separate cells....which is still okay
....but.....
kindly post the same type query where "Physics and Chemistry" are combined into one cell as Course and also
sum the total count for both the "PHY" and "CHM" together into one cell such that it looks like the one below
Course Grp Stype CountofRows Countof...........
Math BOE,LT,DDC,DEC,DEP,DOC,DOH
Math BOE,LT,DDC,DEC,DEP,DOC,DOH
......etc....
Physics,Chemistry BOE,LT,DDC,DEC,DEP,DOC,DOH
Physics,Chemistry BOE,LT,DDC,DEC,DEP,DOC,DOH
Thats because we are doing a group by Stype.
Now, easy enought for that to be removed from the select and the group by, but then it will also affect part of the aggregation. Remmber that the ccon = 'yes' part is simply counting a 1 for an instance of "yes" and the more we consolidate the less meaningful that "yes" counter becomes because we are not actually counting the number of "yes" like we are for "no".
So, if you can describe what we really need to count for "yes" for example,
for each unique combination of course and group and type and ccon="yes" count 1
or
for the consolidated group and course, count the number of "yes" as if those two columns were already consolidate in the raw data
and if more like the second condition, then you will need to let us know what other columns (in the raw data such as date or stypeid) help differentiate or decide the real count of ccon = "yes"
e.g.
If we look at the two results of the top "group by" in the above and the second "group by" in the above, then the second one would indicate that there should be a countofcourseid = 4 if we add those two rows. So, we need to know what we are really counting...
Does that make sense ?
Now, easy enought for that to be removed from the select and the group by, but then it will also affect part of the aggregation. Remmber that the ccon = 'yes' part is simply counting a 1 for an instance of "yes" and the more we consolidate the less meaningful that "yes" counter becomes because we are not actually counting the number of "yes" like we are for "no".
So, if you can describe what we really need to count for "yes" for example,
for each unique combination of course and group and type and ccon="yes" count 1
or
for the consolidated group and course, count the number of "yes" as if those two columns were already consolidate in the raw data
and if more like the second condition, then you will need to let us know what other columns (in the raw data such as date or stypeid) help differentiate or decide the real count of ccon = "yes"
e.g.
Physics BOE OnCampus No
Physics STU OnCampus No
Physics LT OffCampus Yes
Physics DDC OffCampus Yes
Physics DEC OffCampus Yes
Physics DOC OnCampus Yes
Physics DOH OnCampus Yes
if the "group by" yields the following:
Physics,Chemistry BOE,LT,DDC,DEC,DEP,DOC,DOH, DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR
then the counters will be :
#rows = 7
#yes = 5
#No = 2
countofcourseid = 3 ??? (2 for no, 1 for Yes)
Currently including stype in the group by, the query yields the following:
Physics,Chemistry BOE,LT,DDC,DEC,DEP,DOC,DOH, DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR OnCampus
Physics,Chemistry BOE,LT,DDC,DEC,DEP,DOC,DOH, DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR OffCampus
and the counters will be for row 1:
#rows = 4
#yes = 2
#No = 2
countofcourseid = 3 ??? (2 for no, 1 for Yes)
and the counters will be for row 2:
#rows = 3
#yes = 3
#No = 0
countofcourseid = 1 ??? (0 for no, 1 for Yes)If we look at the two results of the top "group by" in the above and the second "group by" in the above, then the second one would indicate that there should be a countofcourseid = 4 if we add those two rows. So, we need to know what we are really counting...
Does that make sense ?
ASKER
Mark:
Judging by your posted query under ID:35763367, I have attached the new expected result in my new uploaded spreadsheet.
Ccon-Sample5.xls
Judging by your posted query under ID:35763367, I have attached the new expected result in my new uploaded spreadsheet.
Ccon-Sample5.xls
bobby6055: it is better to point to a post in a thread using http:#a35763367 rather that ID:35763367. The former will create a pointer for you and when clicked, takes you to the post - the latter just makes everyone else mad ;-)
ASKER
ok
ASKER
Mark:
It is really simple the way you suggested it (http:#a35763367 rather that ID:35763367).
Honestly I did not know until now ... but now I know. Thanks
It is really simple the way you suggested it (http:#a35763367 rather that ID:35763367).
Honestly I did not know until now ... but now I know. Thanks
bobby6055: That was my suggestion at http:#a35765627 - not mark's ;-)
The spreadsheet doesnt really give us enough variation to decide about the aggregation levels for ccon='yes' - there is only one instance of having a yes count > 0
If you can find a few more example where there are ccon = yes for physics and chemistry (as their seperate groups) along with those expected results, we should be fine.
The No count will work fine - it is simply a count, so the real challenge is defining the correct way to calculate the yes count.
if it is counting correctly for the stype grouping, then we might need to put that into a subquery and sum those... e.g.
select course,grp,sum(countofcour
from (
... the other query grouping by stype goes here...
) SQ
group by course,grp
Or we simply do the group by without the stype in the other query.
So, we are still not 100 percent sure which way to go just yet whilst there is only the one example of a ccon = "Yes"
If you can find a few more example where there are ccon = yes for physics and chemistry (as their seperate groups) along with those expected results, we should be fine.
The No count will work fine - it is simply a count, so the real challenge is defining the correct way to calculate the yes count.
if it is counting correctly for the stype grouping, then we might need to put that into a subquery and sum those... e.g.
select course,grp,sum(countofcour
from (
... the other query grouping by stype goes here...
) SQ
group by course,grp
Or we simply do the group by without the stype in the other query.
So, we are still not 100 percent sure which way to go just yet whilst there is only the one example of a ccon = "Yes"
oh, and yes, GRayL posted how to create the shortcut / link to another comment :)
ASKER
GRayL:
Thanks for pointing that out. It's cool.
Thanks for pointing that out. It's cool.
ASKER
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also, looking at the IIF() as GRP think you can do :
Gets rid of one level of IIF()
IIf(tblMain.CourseID in ("PL","PHY","CHM","MTH","LA","RC") And 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",
IIf(tblMain.CourseID="NEW" And tblMain.GroupID In ('CBS','MHL','SPB'),
"CBS,MHL,SPB",
tblMain.GroupID))Gets rid of one level of IIF()
ASKER
Mark:
Thanks, I will spend some time getting digesting your posted solution. I appreciae your assistance on this topic.
Questions:
(a). How do I swap your posted "If Statement" under http:#a35773965 in place of tblCourse.Course as suggested?
(b). How do I swap your posted "IIF() as GRP " under http:#a35773993 into your posted query as well?
I tried doing it myself I received all kinds of errors. I am very poor with SQL...I am not an SQL guy...I must confess.
Thanks, I will spend some time getting digesting your posted solution. I appreciae your assistance on this topic.
Questions:
(a). How do I swap your posted "If Statement" under http:#a35773965 in place of tblCourse.Course as suggested?
(b). How do I swap your posted "IIF() as GRP " under http:#a35773993 into your posted query as well?
I tried doing it myself I received all kinds of errors. I am very poor with SQL...I am not an SQL guy...I must confess.
ASKER
Brilliant
This should go close ;)
select course, grp, sum(countofcourseid) as totalCourseCount
from (
SELECT IIf(tblMain.GroupID In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR') And tblMain.CourseID In ("PHY","CHM"), "Physics, Chemistry", tblCourse.Course) AS Course,
IIf(tblMain.CourseID in ("PL","PHY","CHM","MTH","LA","RC") And 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",
IIf(tblMain.CourseID="NEW" And tblMain.GroupID In ('CBS','MHL','SPB'),
"CBS,MHL,SPB",
tblMain.GroupID)) AS GRP,
tblStype.stype,
count(*) AS countofRows,
sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1)) AS CountOfcconNO,
sum ( iif( nz([CCon],'No') = 'Yes' , 1, 0)) AS CountOfcconYES,
iif ( (sum( iif( nz([CCon],'No') = 'Yes' , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1)) AS CountOfCourseID
FROM ((tblMain
INNER JOIN tblCourse ON tblMain.CourseID=tblCourse.CourseID)
INNER JOIN tblStype ON tblMain.StypeID=tblStype.StypeID)
WHERE tblMain.Appdate >=forms!frmReportDateRange!BeginDate And tblMain.Appdate <=forms!frmReportDateRange!EndDate
AND tblMain.STYpeID In ('CM','SA')
GROUP BY IIf(tblMain.GroupID In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR') And tblMain.CourseID In ("PHY","CHM"), "Physics, Chemistry", tblCourse.Course),
IIf(tblMain.CourseID in ("PL","PHY","CHM","MTH","LA","RC") And 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",
IIf(tblMain.CourseID="NEW" And tblMain.GroupID In ('CBS','MHL','SPB'),
"CBS,MHL,SPB",
tblMain.GroupID)),
tblstype.stype
) SQ
GROUP BY COURSE,GRPASKER
Mark:
Thanks for the http:#a35775184, it was useful.
For now I will digest this "Ccon logic" and the complex queries and my guts feeling is that I might still come up with couple other related questions on the same logic with variations in the future.
Please accept my sincere gratitude for taking your time to assist despite your busy schedule.
Cheers !!!
Bobby
Thanks for the http:#a35775184, it was useful.
For now I will digest this "Ccon logic" and the complex queries and my guts feeling is that I might still come up with couple other related questions on the same logic with variations in the future.
Please accept my sincere gratitude for taking your time to assist despite your busy schedule.
Cheers !!!
Bobby
ASKER
Mark:
Based on the same TEXT type of "Yes and No" coon logic. I have decided to post a "not so involving" quick question at
https://www.experts-exchange.com/questions/27042721/Follow-up-on-the-Text-ccon-field-Logic.html
...while this logic is still very much fresh in our mind.
Based on the same TEXT type of "Yes and No" coon logic. I have decided to post a "not so involving" quick question at
https://www.experts-exchange.com/questions/27042721/Follow-up-on-the-Text-ccon-field-Logic.html
...while this logic is still very much fresh in our mind.
I will be offline for a little while and can look at it when I get back. Think I know what you are looking for.... but....
iif ( (sum( iif( nz([CCon],'No') = 'Yes' , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1)) AS CountOfCourseID,
was a counter for stypeid's we only wanted to count a 1 (not the actual count) for ccon = "Yes" and the actual count for ccon = "No"
However, we have moed toward the UNION view the first query being for ccon = "No" (and we can do the count in there), and the second query being for ccon = "Yes" and we can score a 1 in there.
So, might want to consider this request in light of the other queries.
Will login in a few (all things considered)