Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

Enter a Parameter dialog question

The query in the counts number of records per group using ccon field  WHERECLAUSE within a specified date range.
I now want to count the records within a specified date range but without the ccon whereclause just for comparison purposes.

I eliminated the line below from the query but I am now getting "Enter a Parameter dialog for q3.countcourseID.
iif ( (sum( iif( nz([CCon],'No') = 'Yes' , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1)) AS CountOfCourseID,

How do I resolve this problem and be able to achieve my desired result described above?
SELECT Q3.Course, Q3.Grp, Sum(q3.CountofCourseID) AS countofcourseid
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="PL" 
          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, 

       iif ( (sum( iif( nz([CCon],'No') = 'Yes' , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1)) AS CountOfCourseID, 


      tblmain.STypeid

   FROM tblMain

   INNER JOIN tblCourse ON tblMain.CourseID=tblCourse.CourseID 

   WHERE (tblMain.Appdate)>=forms!frmReportDateRange!BeginDate And (tblMain.Appdate)<=forms!frmReportDateRange!EndDate

   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="PL" 
          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))),

      tblMain.STypeid
) AS Q3
GROUP BY Course, Grp;

Open in new window

0
bobby6055
Asked:
bobby6055
  • 14
  • 7
  • 4
1 Solution
 
peter57rCommented:
The possibilities are..

1)your query does not work at the moment,
or
2) you have have done more than delete the Where clause.
Just deleting the Where clause from this query cannot possibly have any effect on whether it is a valid query or not
or
3) removing the selection has allowed records with invalid data values to be included in the query.


Of these (2) sounds the most likely to me.
0
 
bobby6055Author Commented:
Peter:
Sorry....This question has a related question at: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26987644.html?cid=748#a35533266

There is a sample db at the related link to test my current query which works fine.
0
 
bobby6055Author Commented:
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
peter57rCommented:
Sorry- misread the Q-  you said you had  removed the Where clause but that's not what you've done.
I'll look again.
0
 
bobby6055Author Commented:
I posted the complete query in this question for experts to see....but in my own test I did remove this line from the query.

iif ( (sum( iif( nz([CCon],'No') = 'Yes' , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1)) AS CountOfCourseID,
0
 
peter57rCommented:
When you remove the line , you are removing the definition of q3.CountOfCourseID, so you cannot include it in the initial Select on line 1.

So you have to remove
Sum(q3.CountofCourseID) AS countofcourseid
from line 1

And you  would just use..

SELECT Q3.Course, Q3.Grp



0
 
bobby6055Author Commented:
I still want to be able to count each group without the ccon field whereclause. Your last suggestion eliminated the counts for each group without the ccon field.
0
 
peter57rCommented:
All I was doing was explaining why you are getting the parameter prompt.

I can't tell you how to get the count you want as I don't know anything about the data.

0
 
Mark WillsTopic AdvisorCommented:
what grouping are you trying to get a count of ? Are you counting the number of stypeid's , or the total number of rows ? Not exactly sure from the above, so, what about :


SELECT Q3.Course, Q3.Grp, Sum(q3.CountofCourseID) AS countofcourseid, sum(q3.number_of_stypeid) as countofstypeid, sum(q3.number_of_rows) as countofrows
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="PL" 
          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, 

       iif ( (sum( iif( nz([CCon],'No') = 'Yes' , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1)) AS CountOfCourseID, 

       1 as number_of_STypeid,

       count(*) as number_of_rows,

      tblmain.STypeid

   FROM tblMain

   INNER JOIN tblCourse ON tblMain.CourseID=tblCourse.CourseID 

   WHERE (tblMain.Appdate)>=forms!frmReportDateRange!BeginDate And (tblMain.Appdate)<=forms!frmReportDateRange!EndDate

   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="PL" 
          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))),

      tblMain.STypeid
) AS Q3
GROUP BY Course, Grp; 

Open in new window

0
 
bobby6055Author Commented:
Mark:
You asked:
"what grouping are you trying to get a count of ? ....."

My response:
I am trying to count the number of CourseIDs "without" the test for ccon. In other words I want the count of the CouseID but eliminate the ccon  whereclause completely.

For this test we can use the following test dates.

Begindate = 12/01/09 (mm dd yy)
Enddate    = 12/31/09 (mm dd yy)

The query you provided under ID #35715427 still utilizes the ccon logic...Let's get rid of ccon completely - just a simple count queryper course is sufficient.

Thanks for your assistance.

Bobby




0
 
Mark WillsTopic AdvisorCommented:
OK, so just the number of courseID's (or really the number of courseid + grp combinations ? )

So whatabout :

SELECT Q3.Course, Q3.Grp, sum(q3.number_of_courseid) as countofcourseid
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="PL" 
          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, 

       1 as number_of_courseid

   FROM tblMain

   INNER JOIN tblCourse ON tblMain.CourseID=tblCourse.CourseID 

   WHERE (tblMain.Appdate)>=forms!frmReportDateRange!BeginDate And (tblMain.Appdate)<=forms!frmReportDateRange!EndDate

   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="PL" 
          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 Q3
GROUP BY Course, Grp; 

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Hmmm... nah... dont think the above is what you want.  Probably end up being a 1 for everything.

gotta be a count of something within courseid maybe. maybe a count of stypeid ?

From the previous query, did any of the sum(number_of_...) match the requirement ?
0
 
Mark WillsTopic AdvisorCommented:
if we want to get rid of the ccon logic and just count, then is this what you are after ?


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="PL" 
          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, 

       count(*)  as countofcourseid

   FROM tblMain

   INNER JOIN tblCourse ON tblMain.CourseID=tblCourse.CourseID 

   WHERE (tblMain.Appdate)>=forms!frmReportDateRange!BeginDate And (tblMain.Appdate)<=forms!frmReportDateRange!EndDate

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

Open in new window

0
 
bobby6055Author Commented:
Let me study this a little please
0
 
bobby6055Author Commented:
I have tried all your suggested Queries above. The one that comes closer is your very last query under ID:35715560.

The Query seems okay EXCEPT that of GROUP ("PHY", "CHM"). The result of it is badly formatted . (See the attached excel file).

Also in that excel sheet, you will find the correct format expected.
Grouped-Excel-Sample-01a.xls
0
 
bobby6055Author Commented:
In order to get the output Excel file above, I tweaked my sample db.
Please download my new sample db (now attached) and run your most recent query using

12/01/09 as Begindate (mm/dd/09)  and....
12/31/09 as End date
Query-Sample2.mdb
0
 
Mark WillsTopic AdvisorCommented:
OK that is because the test for the combination of :

tblMain.GroupID In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR') And tblMain.CourseID In ("PHY","CHM")

only exists for creating the Course. There is no such comparison for Group.

We do however have :

tblMain.CourseID="PL" And tblMain.GroupID In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')

should we change : tblMain.CourseID="PL"
to :  tblMain.CourseID in ("PL","PHY","CHM")

that would do the GRP transformation the way the spreadsheet shows.


e.g.

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, 
          count(*) AS countofcourseid

FROM      tblMain 
INNER JOIN tblCourse ON tblMain.CourseID=tblCourse.CourseID

WHERE    (tblMain.Appdate)>=forms!frmReportDateRange!BeginDate And (tblMain.Appdate)<=forms!frmReportDateRange!EndDate

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

Open in new window


0
 
bobby6055Author Commented:
Mark:
"Physics, and Chemistry are group together,
Can we have your query to populate the date separately such that the group are listed like the one below:

GRP                                                                                                               Course                     countofcourseid
BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR           Math                              54
CBS,MHL,SPB                                                                                                Phychology                    5
BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR          Physics                           8
BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR          Chemistry                       2
BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR          Religion                          8
0
 
Mark WillsTopic AdvisorCommented:
Yep, just need to change that first IIF() to be simply the course...

But your spreadsheet shows them grouped.

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, 
          count(*) AS countofcourseid

FROM      tblMain 
INNER JOIN tblCourse ON tblMain.CourseID=tblCourse.CourseID

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

Open in new window

0
 
bobby6055Author Commented:
Finally,
In you query under ID:35717232, I have this hypothesis that I am not sure can work but I am asking you if it is possible
to further provide a breakdown of the counts using "tblSType.SType" since tblMain.SType is already part of the query
...such that the output looks like the one below:

...something like....
IIf(tblMain.CourseID="PL"
           AND tblMain.STYpeID In ('CM','SA','ST'),                                                                                               "<---------------------- Added here
          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",


GRP                                                                                                               Course                      SType                  countofcourseid
BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR           Math                         OnCampus                      19
BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR           Math                         OffCampus                      11
BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR           Math                         Online                             24

CBS,MHL,SPB                                                                                                Phychology               OnCampus                      2
CBS,MHL,SPB                                                                                                Phychology               OffCampus                      1
CBS,MHL,SPB                                                                                                Phychology               OnCampus                      2

BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR          Physics                     OnCampus                      7
BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR          Physics                     OffCampus                      1

BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR          Chemistry                OnCampus                      2

BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR          Religion                   OnCampus                       5
BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR          Religion                   OffCampus                       2
BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR          Religion                   Online                              1
 
0
 
bobby6055Author Commented:
Mark:
I already awarded you based on your last post.
I will be grateful if you would kindly still post suggestion to my last question under ID:35717528
which will enable me determine the possibility of a future new EE question as followup.
0
 
Mark WillsTopic AdvisorCommented:
Sure, just add in stype into both the select and the group by...

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
 
bobby6055Author Commented:
Thanks
0
 
bobby6055Author Commented:
Actually, I was hoping you would specify the specific items to look for in the output.

something like this...

AND tblMain.STYpeID In ('CM','SA'),         '<----------- I want the flexibility to remove or add items to pull in the table

Anyway, I have opened a new EE question to address that hoping that you would assist further.....and this time I want to add the "ccon" test back into the new query as well.

Cheers
Bobby
0
 
bobby6055Author Commented:
Here is the link for the new question hoping you would further assist.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27026083.html
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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