troubleshooting Question

Need help w/ modifying SQL statement (2 criteria in HAVING clause)

Avatar of TomBock2004
TomBock2004 asked on
Microsoft Access
4 Comments1 Solution295 ViewsLast Modified:
I think I have a simple VBA SQL modification question:

This function (between ***s) works perfect, but I do require some help w/ adding a 2nd criteria into my HAVING clause (MySQL_2):

- all I need to add is: >> AND ((Avg(tblSurveysAnswers.Answer))<=5) <<

I hope it's as easy as:
 "HAVING tblSurveys.FOGOLevel In (" & Me!cboBranchID.Column(4) & ") AND ((Avg(tblSurveysAnswers.Answer))<=5)"

However, no matter how I do it, I get syntax errors... it might be the number of opening/closing parenthesis.

Any ideas?
Tom


*****************************************************************
MySql = "SELECT tblSurveys.FOGOLevel, tblSurveys.Subdivision, " & _
            "tblKeyPerformanceIndicators.KPIpk, tblKeyPerformanceIndicators.KPIName, " & _
            "tblSurveysAnswers.IndexID, tblIndices.IndexName, tblSurveysAnswers.IndexQuestionID, " & _
            "tblQuestions.Question, Avg(tblSurveysAnswers.Answer) AS AvgOfAnswer, " & _
            "QuarterID([DTSSurveyStart]) AS GetQuarterID, Year([DTSSurveyStart]) " & _
            "AS [Year] FROM (tblKeyPerformanceIndicators INNER JOIN tblIndices " & _
            "ON tblKeyPerformanceIndicators.KPIpk = tblIndices.KPIpk) INNER JOIN " & _
           "(tblSurveys INNER JOIN (tblQuestions INNER JOIN tblSurveysAnswers ON " & _
           "(tblQuestions.IndexQuestionID = tblSurveysAnswers.IndexQuestionID) AND " & _
           "(tblQuestions.IndexID = tblSurveysAnswers.IndexID)) ON " & _
           "tblSurveys.ParticipantID = tblSurveysAnswers.ParticipantID) ON " & _
           "tblIndices.IndexID = tblQuestions.IndexID GROUP BY tblSurveys.FOGOLevel, " & _
           "tblSurveys.Subdivision, tblKeyPerformanceIndicators.KPIpk, " & _
           "tblKeyPerformanceIndicators.KPIName, tblSurveysAnswers.IndexID, " & _
           "tblIndices.IndexName, tblSurveysAnswers.IndexQuestionID, tblQuestions.Question, " & _
           "QuarterID([DTSSurveyStart]), Year([DTSSurveyStart]) "

MySQL_2 = "HAVING tblSurveys.FOGOLevel In (" & Me!cboBranchID.Column(4) & ") "
*****************************************************************
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros