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

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) & ") "
*****************************************************************
TomBock2004Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
try leaving MOST of the (..) off:

"HAVING tblSurveys.FOGOLevel In (" & Me!cboBranchID.Column(4) & ") AND Avg(tblSurveysAnswers.Answer)<=5"

the ones that I eliminated are totla extraneous.

AW

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Arthur_WoodCommented:
the problem may be that you are tryuing to use an Aggregate function (AVG) function in the Having clause, but the Having will use of the aggregate function does not limit any specific records.  What are you trying to accomplish with the added clause, in the Having portion?

do you want to eliminate those records from tblSurveysAnswers  that have an Answer value that is less than the Average of all of the answers?

"HAVING tblSurveys.FOGOLevel In (" & Me!cboBranchID.Column(4) & ") AND tblSurveysAnswers.Answer < = Avg(tblSurveysAnswers.Answer)"

or perhaps:


"HAVING tblSurveys.FOGOLevel In (" & Me!cboBranchID.Column(4) & ") AND tblSurveysAnswers.Answer <=5"

???

AW



TomBock2004Author Commented:
Arthur,

"HAVING tblSurveys.FOGOLevel In (" & Me!cboBranchID.Column(4) & ") AND Avg(tblSurveysAnswers.Answer)<=5"

... works perfect!!!    I knew this should have been an easy one.  

Thousand thanks for keeping it simple!
Tom
Arthur_WoodCommented:
glad to be of assistance

AW
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.