Error in a Query

Nothing wrong and no errors here except for the missing form(!).

Make sure that the fields of the form holds valid dates (no Nulls) before calling the query.

/gustav

Please download my sample db and create a new QUERY and paste my above posted Query into it.

You will not be able to save the query and you will definetly get a "Syntax error in FROM Clause"

Your query contains many errors and as you don't tell what you wish to achieve, it is close to impossible to correct.

Start with the inner queries and get these to operate as separate saved queries.
Then collect these in a new union query and get this to work.
Finally, apply the outer query or - better - create a new query where you use the union query as the single source.

/gustav

gustav:
I am really very bad with SQL - I am not an SQL guy. I will appreciate it if you would post the sample queries

gustav
If you post the breakdown and the final complex, I will probably learn the process of dismantling and recomposition of complex queries.

There were a couple slight discrepencies for the group by clause; the first portion of the select IIF had:
('BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR')

...but the corresponding Group by reference had:
('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')

...since those weren't the same it was causing an error.  There also seemed to be some other slight discrepencies, i think with a paren...lost track, it was a lengthy query :)  

Anyway, here's the modified code below that should work, just paste it into your query,...and as the previuos expert mentioned, make sure your form is running otherwise it will prompt you for the dates.

SELECT TM.Course, TM.GRP, Sum(TM.CountOfCourseID) AS CountOfCourseID 
FROM(SELECT  
    
      IIf(tblMain.GroupID In ('BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR') 
 
          And tblMain.CourseID In ("PHY","CHM"), 
          "Physics, Chemistry", 
          tblCourse.Course) AS Course,  
 
 
      IIf(tblMain.CourseID="PL"  
          And tblMain.GroupID In ('BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR'), 
          "'BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','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','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR'), 
          "'BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR'",  
                   tblMain.GroupID))) AS GRP,  
 
      Count(*) 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 And Not nz([CCon],False)
   
      GROUP BY  
 
      IIf(tblMain.GroupID In ('BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR') 
 
          And tblMain.CourseID In ("PHY","CHM"), 
          "Physics, Chemistry", 
          tblCourse.Course) ,  
 
 
      IIf(tblMain.CourseID="PL"  
          And tblMain.GroupID In ('BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR'), 
          "'BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','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','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR'), 
          "'BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR'",  
                   tblMain.GroupID)))
				   
				   ,tblMain.STypeid
				   
	UNION
	
	SELECT  
    
      IIf(tblMain.GroupID In ('BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR') 
 
          And tblMain.CourseID In ("PHY","CHM"), 
          "Physics, Chemistry", 
          tblCourse.Course) AS Course,  
 
 
      IIf(tblMain.CourseID="PL"  
          And tblMain.GroupID In ('BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR'), 
          "'BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','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','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR'), 
          "'BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR'",  
                   tblMain.GroupID))) AS GRP,  
 
      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 And Not nz([CCon],False)
   
      GROUP BY  
 
      IIf(tblMain.GroupID In ('BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR') 
 
          And tblMain.CourseID In ("PHY","CHM"), 
          "Physics, Chemistry", 
          tblCourse.Course) ,  
 
 
      IIf(tblMain.CourseID="PL"  
          And tblMain.GroupID In ('BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR'), 
          "'BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','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','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR'), 
          "'BOE','CED','DDC','DEC','DEP','DOC','DOH','DOS','DOT','DWA','DWT','GEN','MTA','PAU','PKS','TTR'",  
                   tblMain.GroupID)))
				   
				   ,tblMain.STypeid

				   ) AS TM 
GROUP BY TM.Course, TM.GRP;

Select allOpen in new window

vbNewbie2009:  Nice work!  That's going the extra mile.

vbNewbie2009:
Thanks for pointing out errors, Your posted query works hpwever I want to be able t tace the errors myself.

Starting from my original SQL posting, I tried tracing the errors myself but was not quite successful.

First in the GROUP BY section of the query, I changed this line:

GROUP BY

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

TO......
GROUP BY

      IIf(tblMain.GroupID In (‘BOE’,’CED’,’DDC’,’DEC’,’DEP’,’DOC’,’DOH’,’DOS’,’DOT’,’DWA’,’DWT’,’GEN’,’MTA’,’PAU’,’PKS’,’TTR’
          And tblMain.CourseID In ("PHY","CHM"),
          "Physics, Chemistry",
          tblCourse.Course) ,
***********************

However, I was unable to figure out the errors. Would you knidly tell me where the other errors are located from my original query.

Cheers,
Bobby

 

Basically, if you are using non-aggregated fields in the select, then they have to appear exactly the same way in the group by (or be aggregated).

So, if you have a compound / calculated field like the iif() ones, then you do need to repeat them exactly - except without the alias (e.g. removing the "AS GRP" bit ). Always best to copy and paste then remove the alias.

Mark:
I appreciated having corrected the error but my original question was:

QUESTION:
1. Is there a way to improve on this query since it's a little too ong?
2. Is there a better way to recreate this query.

In other words, is it possible can the alias be changed to somerhing else....?
 in order to reduce the length of my original query.

The type of data result of depends on the [Ccon] field ,,,very important.

Is there a better way to rewrite the [CCon] field in the query
 e.g iff([Ccon=Yes],False,True)

...such that [Ccon] field becomes the primay focus or a determining factor of this query in obtaining the accurate data that I wanted?

The "Yes and No" field sometimes could be "text" field instead of a checkbox.

It may also be a checkbox intepreted as (0,1), (True,False) etc.

Rge bottom line is how do I embed this iff statement

iff([Ccon=Yes],False,True)

 ......into my original query?

So my questions are?
(1). Can the query "aliases" be changed to something else to reduce the length?
(2). Can I utilize the iff statement..... iff([Ccon=Yes],False,True)
         ..... in my query?

I will appreciate a new query with the above suggestions in it?

Mark:

In your posted query:
iif ( (sum( iif( nz([CCon],False) , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],False) , 0, 1)) AS CountOfCourseID,

      tblmain.STypeid

Can we now reframe it with ...  iff([Ccon=Yes],False,True)
assuming that the "Yes/No" is defibed as a text field in the table  rather than a checkbox. All I wanted is the flexibility to use the "Yes/No" value if it is defined as a "text" field in the table.

Mark:
Lastly can we redefine the datestring at this line...using "cdate ?

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

I can give it another once over to help you determine where your errors were, for learning purposes, i'll take a look at it this evening and get back to you.

You ask if the query could be made simpler,...it seems you are specifying if a group is IN a list quite often, but looking at the group list it would probably be shorter to specify NOT IN.

Please describe in a sentence or two what the query results you expect and I'll look at a shorter option too.

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

If begindate and enddate are defined as dates, then there is no real need to use things like cdate() function, can simply use the date fields "natively" when used to compare with appdate. This is not like a previous / different question where you needed to pass those fields as arguments.

>> iff([Ccon=Yes],False,True)

If ccon is being defined as something other than a yes/no then we would need to change that away from using boolean logic and start using string comparisons. at the moment we can use it directly as a boolean result like "iif ( ccon , true , false ) type of comparison. So, if you want to move away from the currrent datatype then we would need to know what the new column would be declared as (and the values).

>> Can the query "aliases" be changed to something else to reduce the length?

The length has been reduced - see my code example above. The other way is to put those "calculated" columns into a view/query and then run your query over the top of that. You could also put those big iif() type fields into another table and then join to that other table to get the required result ( ie put courseid and groupid into a lookup table so we can return the corrected / consolidated new group and course - might depend on the different combinations available as to exact / preferred approach).

Oh, and if you do seomthing like create a query over tblmain to define/accommodate those big iff() columns, you could also "manage" the ccon column in that query as well.

e.g. create qry_tblmain to present the columns the way you want then your following query becomes somehing more like :
select cCourse, cGroup from qry_tblmain group by cCourse, cGroup
obviously a lot more to do for the counters, but you no longer have to do those big iif() if they are already defined in qry_tblmain (maybe via lookup or via iif() )

I am open to all options:
Mark:
(1).
You said: "If begindate and enddate are defined as dates, then there is no real need to use things like cdate() function"

My response:
We can define or specify #datestrings# e.g #dd/mm/yy# as part of the query would be superb.

(2). Issue of the "Yes/No" text field.

In my sample db ccon field is defined as a "checkbox" field. However, in the real database, it's a text box that store "Yes" or "No"
from a COMBO box of an entry form. So inmy database, ccon is definitely a text box that store a "Yes" or "No" text  - not a "checkbox" at all.

So defining the new "query" to pull a Yes" or "No" data from tblMain.ccon (as a text field) will be great.

(3).
You said:
"....The other way is to put those "calculated" columns into a view/query and then run your query over the top of that. You could also put those big iif() type fields into another table and then join to that other table to get the required result ( ie put courseid and groupid into a lookup table so we can return the corrected / consolidated new group and course - might depend on the different combinations available as to exact / preferred approach)."

My response:
It would be great to see the sample dbs of two options you are suggesting....and I will appreciate your effort on those suggested idea.

I am looking for a more accurate, fool proof suggestions.

Cheers !
Bobby

vbNewbie2009:
Based on Marks suggestion under ID:35502566

I copied my working query into a Microsoft word and used the "FIND and REPLACE function to find and replace the "ALIASES". I tested it and it works afterwoards.

What I am now trying to do now is address these questions in my original post:
QUESTION:
1. Is there a way to improve on this query since it's a little too long?
2. Is there a better way to recreate this query

You can now join to assist with a new "query" in that regard
Thanks

Bobby

Mark:
I forgot to tell you that I tested your posted query under ID:35502515 (Begindate = 12/01/09: End Date = 12/31/09) as follows:

(a). First I ran your query while tblMain.ccon is a "checkbox" and I potained the following result.
      Course                           Grp                                                                                                                        countofcourseid
        Gym                                  STU                                                                                                                                 3
        Math                            BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR                                 36
       Physics, Chemistry       STU                                                                                                                                 10


(b). I also conducted the same query having chaned the tblMain.ccon to a test field containing "Yes" and "No" text data
      the result I obtained was way off my first result which re-iterated my desire for a text type ("Yes" and "No")  tblMain.ccon

       Course                           Grp                                                                                                                        countofcourseid
        Gym                                   STU                                                                                                                                 1
        Math                             BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR                                 3
        Physics, Chemistry       STU                                                                                                                                 2

My office db is designed to store a text type "Yes and No". Of course it could have designed as a "checkbox" but it was
intensionally designed as a TEXT box to enforce certain office scenerio.

ooops...typo...

(b). I also conducted the same query having changed the tblMain.ccon to a text field containing "Yes" and "No" text data
      the result I obtained was way off my first result which re-iterated my desire for a text type ("Yes" and "No") query
      that caters for  tblMain.ccon's design.

       Course                                Grp                                                                                                                        countofcourseid
        Gym                                   STU                                                                                                                                  1
        Math                                  BOE,LT,DDC,DEC,DEP,DOC,DOH,DOS,DOT,EDT,STU,MTA,PAU,ASL,TTR                             3
        Physics, Chemistry            STU                                                                                                                                   2

I know that this part of your query is designed specifically for a "checkbox"

iif ( (sum( iif( nz([CCon],False) , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],False) , 0, 1)) AS CountOfCourseID,

Mark:
Thanks for this extensive solution set. I think I still your single query solution better with your suggested 3 character text field for CCOn

You said in your last post  
" .......Notice we cannot get rid of the subquery because the "definition" for the count has to take into account the STypeid
(and therefore need to gain the "base" counters grouped by stypeid)"

What if we further amend the "SINGLE QUERY SOLUTION" to further restrict the data to be pulled...???

As such I will appreciate one final single query solution that includes "......... AND tblMain.STypeID In ("CM", "SA", "ST")"
I tried the one posted below but I got all types of errors. Kindly assist by correcting it.

e.g something like this.. I am not too sure I did it right, I just guessed but I got so many errors.
***************
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") AND tblMain.STypeID In ("CM", "SA", "ST"),                                       '<----------- Added here
                                                                        ^^^^^^^^^^^^^^^^^^^^^^^^^^
          "Physics, Chemistry",
          tblCourse.Course) AS Course,

      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",
   
           IIf(tblMain.CourseID="NEW" AND tblMain.STypeID In ("CM", "SA", "ST"), And tblMain.GroupID In ('CBS','MHL','SPB'),"CBS,MHL,SPB",    '<----- Added here
                                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
               IIf(tblMain.CourseID In ("MTH","LA","RC") 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",
                   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,     "<------- 3 character text field for CCON

      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") AND tblMain.STypeID In ("CM", "SA", "ST"),                                       '<----- Added here,
          "Physics, Chemistry",                              ^^^^^^^^^^^^^^^^^^^^^^^^^^
          tblCourse.Course),

      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",
   
           IIf(tblMain.CourseID="NEW" AND tblMain.STypeID In ("CM", "SA", "ST")                               '<----------- Added here
                                                        ^^^^^^^^^^^^^^^^^^^^^^^^^^
             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
) Q3
Group by Course, Grp

Excellent !!!

>> What if we further amend the "SINGLE QUERY SOLUTION" to further restrict the data to be pulled...???

Well, it is more about the grouping by stypeid for the counting part.

So, what we are trying to do is to count 1  if CCON = 'Yes' for any of the stypeid's . Imagine for a second that there were CM and SA and ST - that should count as 3 and so we need to somehow do a group by for the distinct values. If we try to include that somehow and then group by the resulting Corse and Grp then we wont really get a count of 3 - the aggregation gets lost.

Does that make sense ?

Yes.

Mark:
Thanks for your efforts despite your busy schedule. It's very much appreciated.

Bobby

Mark:
Out of curiousity, I removed the test for "Ccon" field with a view to testing the query without ccon logic.

 iif ( (sum( iif( nz([CCon],'No') = 'Yes' , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1)) AS CountOfCourseID,           '<-------- line removed from Query entirely for my curiousity test

When I tested the query below, I did not get any error but I did get the "Enter Parameter dialog box" for countofCourseID.

I am trying to compare the data populated with or without the [Ccon] field
                                                                    ^^^     ^^^^^

Please assist in posting the correct query "WITHOUT" the test for Ccon field.

Cheers

Bobby

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

Select allOpen in new window

Mark:
I have created a new EE question to address my last request. So please ignore my last request here.

You may assist me in the new post.

Well, the reason for : "Enter Parameter dialog box" for countofCourseID is because removing the ccon test/aggregation means there is no longer any definition for it so it is being regarded as a parameter.

Will have a look at your other question.