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

Error in a Query

The query in the code snippet previously worked for me but now I am now receiving SYNTAX ERROR in FROM CLAUSE.

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

Open in new window

Query-Sample.mdb
0
bobby6055
Asked:
bobby6055
  • 19
  • 7
  • 2
  • +2
2 Solutions
 
Gustav BrockCIOCommented:
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
0
 
bobby6055Author Commented:
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"
0
 
Gustav BrockCIOCommented:
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
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
bobby6055Author Commented:
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
0
 
bobby6055Author Commented:
gustav
If you post the breakdown and the final complex, I will probably learn the process of dismantling and recomposition of complex queries.
0
 
vbNewbie2009Commented:
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;

Open in new window

0
 
GRayLCommented:
vbNewbie2009:  Nice work!  That's going the extra mile.
0
 
bobby6055Author Commented:
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

 
0
 
Mark WillsTopic AdvisorCommented:
Dont really need the union...

Please have a look at :

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],False) , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],False) , 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
) Q3
Group by Course, Grp

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
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.
0
 
bobby6055Author Commented:
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?
0
 
bobby6055Author Commented:
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?
0
 
bobby6055Author Commented:
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.

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

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

0
 
vbNewbie2009Commented:
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.
0
 
Mark WillsTopic AdvisorCommented:


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



0
 
Mark WillsTopic AdvisorCommented:
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() )
0
 
bobby6055Author Commented:
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


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

0
 
bobby6055Author Commented:
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
0
 
bobby6055Author Commented:
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,
0
 
Mark WillsTopic AdvisorCommented:


Well, if CCON is a 3 character text field then the equivelent would be :

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

If we were to create a new query to do all the hard work in forming the data the way we want (and therefore a single point of maintenance), then it might look like :

SELECT tblMain.GroupID, tblMain.CourseID, tblMain.STypeID, tblMain.CCon, tblMain.Appdate,

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

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

Open in new window


And then our aggregate query becomes a lot more straight forward like :

SELECT Q3.Course, Q3.Grp, Sum(q3.CountofCourseID) AS countofcourseid
FROM (

      SELECT C_Course as Course, C_GRP as GRP, STypeid, iif ( (sum( iif( nz([CCon],'No') = 'Yes' , 1, 0))) > 0 , 1, 0) + sum ( iif( nz([CCon],'No') = 'Yes' , 0, 1)) AS CountOfCourseID

      FROM QryMain

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

      GROUP BY C_Course, C_GRP, STypeid
) AS Q3

GROUP BY Course, Grp;

Open in new window


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)

As for the lookup table to resolve the calculated columns, then we first need to create that table... So, lets assume we have built a table "tblCrouseGroup" with a key of groupid and courseid for those groups and courses that need translation. It also needs the "answers" for that translation so we call them C_GRP and C_Course. Our table therefore has four columns of just those courses/group combos that result in either a new group, a new course or both.

Then our query would look something like :

SELECT Q3.Course, Q3.Grp, Sum(q3.CountofCourseID) AS countofcourseid
FROM (

      SELECT nz(tblCourseGroup.C_Course,tblcourse.Course) as Course, nz(tblCourseGroup.C_GRP,tblmain.Groupid) as GRP, STypeid, 
             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)
      LEFT JOIN tblCourseGroup ON tblMain.CourseID=tblCourseGroup.CourseID and tblMain.Groupid=tblCourseGroup.Groupid

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

      GROUP BY nz(tblCourseGroup.C_Course,tblcourse.Course), nz(tblCourseGroup.C_GRP,tblmain.Groupid), STypeid
) AS Q3

GROUP BY Course, Grp;

Open in new window



0
 
bobby6055Author Commented:
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
0
 
bobby6055Author Commented:
Excellent !!!
0
 
Mark WillsTopic AdvisorCommented:
>> 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 ?
0
 
bobby6055Author Commented:
Yes.
0
 
bobby6055Author Commented:
Mark:
Thanks for your efforts despite your busy schedule. It's very much appreciated.

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

Open in new window

0
 
bobby6055Author Commented:
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.
0
 
Mark WillsTopic AdvisorCommented:
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.
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.

  • 19
  • 7
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now