• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

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

Open in new window

0
bobby6055
Asked:
bobby6055
  • 12
  • 8
  • 4
2 Solutions
 
Mark WillsTopic AdvisorCommented:
Hi bobby,

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)
0
 
GRayLCommented:
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.
0
 
bobby6055Author Commented:
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.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
GRayLCommented:
It looks like the 'office policy' is encouraging bad design habits.  I'm glad I don't work there ;-)
0
 
bobby6055Author Commented:
Mark:
I will like to experiment your suggested idea under ID:35719850.

Cheers

Bobby
0
 
Mark WillsTopic AdvisorCommented:
OK,
We can experiment...

Not entirely sure of the full requirement (there have been a lot of variations here in EE), but as a starting point, lets use the code above

1) the requirement to include : "  AND tblMain.STYpeID In ('CM','SA')  "  we do a "group by" stype so only rtypes for CM and SA will show. But, you might have been suggesting something else (like combine those two values).

2) The code " iif ( (sum( iif( nz([CCon],'No') = 'Yes' , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1)) AS CountOfCourseID "   is scoring a 1 for ccon = yes otherwise it is effectively counting ccon = no. That way we are essentially counting the group (or the number for ccon = yes

3) Have included (outdented) 3 control counters so you can visualise what is happening with that complex sum using the iif() statement - basically component counts so you can compare.

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

Open in new window

0
 
bobby6055Author Commented:
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, DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR       OnCampus         27                    ...etc....
Math                            BOE,LT,DDC,DEC,DEP,DOC,DOH, DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR       OffCampus         15                    ...etc....
......etc....
Physics,Chemistry        BOE,LT,DDC,DEC,DEP,DOC,DOH, DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR       OnCampus         9                     ...etc....          '<--- (Phy and Chem) both are combined here
Physics,Chemistry        BOE,LT,DDC,DEC,DEP,DOC,DOH, DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR       OffCampus         1                    ...etc....           '<--- (Phy and Chem) both are combined here
0
 
Mark WillsTopic AdvisorCommented:
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.

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)

Open in new window


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 ?

0
 
bobby6055Author Commented:
Mark:
Judging by your posted query under ID:35763367, I have attached the new expected result in my new uploaded spreadsheet.
Ccon-Sample5.xls
0
 
GRayLCommented:
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 ;-)
0
 
bobby6055Author Commented:
ok
0
 
bobby6055Author Commented:
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
0
 
GRayLCommented:
bobby6055:  That was my suggestion at http:#a35765627 - not mark's ;-)
0
 
Mark WillsTopic AdvisorCommented:
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(countofcourseid) as totalcount
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"
0
 
Mark WillsTopic AdvisorCommented:
oh, and yes, GRayL posted how to create the shortcut / link to another comment :)
0
 
bobby6055Author Commented:
GRayL:

Thanks for pointing that out. It's cool.
0
 
bobby6055Author Commented:
Mark:
Okay,
Please find another example (attached)
Ccon-Sample6.xls
0
 
Mark WillsTopic AdvisorCommented:
OK, then the easiest (?) way is to do the subquery thingy...

select course, grp, sum(countofcourseid) as totalCourseCount
from (

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

) SQ

GROUP BY COURSE,GRP

Open in new window



Also noticed that your query from the top is using tblcourse.course

Seem to recall another interesting IIF() statement that you might want to consider using instead of tblcourse.course (based on your output results in the spreadsheet). Could also be the the field "course" now contains the group name, bu thought I would mention it all the same... See :

      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, 

Open in new window


You could simply swap the above into your query in place of tblcourse.course  (leaving out the alis in the group by) as per :

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

) SQ

GROUP BY COURSE,GRP

Open in new window


And the above is probably the one you need to test first me thinks....
0
 
Mark WillsTopic AdvisorCommented:
Also, looking at the IIF() as GRP think you can do :

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

Open in new window


Gets rid of one level of IIF()
0
 
bobby6055Author Commented:
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.
0
 
bobby6055Author Commented:
Brilliant
0
 
Mark WillsTopic AdvisorCommented:
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,GRP

Open in new window

0
 
bobby6055Author Commented:
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
0
 
bobby6055Author Commented:
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

http://www.experts-exchange.com/Microsoft/Applications/Q_27042721.html


...while this logic is still very much fresh in our mind.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 12
  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now