Complicated query will not run

Here is the very complicated SQL code of a query I am trying to run.  But when I run it I get a message... "This expression is typed incorrectly, or it is to complex to be evaluated....."  Can someone see what I'm doing wrong?

SELECT [Participant Nat'l Data Table].[Group Number], Count([Participant Nat'l Data Table].[Group Number]) AS [Count], Avg([Participant Nat'l Data Table].PREINDknowAvg) AS Leadership_Pre, Avg([Participant Nat'l Data Table].PostAvgINDKOL) AS Leadership_PostT, Avg([Participant Nat'l Data Table].Leadership_PostP) AS AvgOfLeadership_PostP, Avg([PREINDknowAvg]/[Leadership_PostP]) AS Leadership_Change, Avg([Participant Nat'l Data Table].PREINDmissAVG) AS Mission_Pre, Avg([Participant Nat'l Data Table].POstAvgINDmiss) AS Mission_PostT, Avg([Participant Nat'l Data Table].Mission_PostP) AS AvgOfMission_PostP, Avg([PREINDmissAVG]/[Mission_PostP]) AS Mission_Change, Avg([Participant Nat'l Data Table].PreINDAvgDVKOI) AS DomesticV_Pre, Avg([Participant Nat'l Data Table].PostINDAvgDVKOI) AS DomesticV_PostT, [Participant Nat'l Data Table].DomesticV_PostP, Avg([PreINDAvgDVKOI]/[DomesticV_PostP]) AS DomesticV_Change, Avg([Participant Nat'l Data Table].EC1_pre) AS Satisfaction_Pre, Avg([Participant Nat'l Data Table].EC1_post) AS Satisfaction_PostT, [Participant Nat'l Data Table].Satisfaction_PostP, Avg([EC1_Pre]/[Satisfaction_PostP]) AS Satisfaction_Change, Avg([Participant Nat'l Data Table].EC2_pre) AS AvgOfEC2_pre, Avg([Participant Nat'l Data Table].EC2_post) AS AvgOfEC2_post, Avg([EC2_Post]/[EC2_Pre]) AS EC2AvgChg, Avg([Participant Nat'l Data Table].EC3_pre) AS AvgOfEC3_pre, Avg([Participant Nat'l Data Table].EC3_post) AS AvgOfEC3_post, Avg([EC3_Post]/[EC3_Pre]) AS EC3AvgChg, [Participant Program Entry Table].StartDate, [Participant Program Entry Table].EndDate
FROM [Participant Program Entry Table] INNER JOIN [Participant Nat'l Data Table] ON [Participant Program Entry Table].[Participant Number] = [Participant Nat'l Data Table].[Participant Number]
GROUP BY [Participant Nat'l Data Table].[Group Number], [Participant Nat'l Data Table].DomesticV_PostP, [Participant Nat'l Data Table].Satisfaction_PostP, [Participant Program Entry Table].StartDate, [Participant Program Entry Table].EndDate
HAVING ((([Participant Program Entry Table].EndDate) Between [Forms]![frmReportDateParameter]![txtStart] And [Forms]![frmReportDateParameter]![txtEnd]));

Open in new window


Please note:  It runs fine if I delete the last of three test records in the table "Participant Nat'l Data Table" which has several zero's in some of the fields.  It also runs fine if I replace the zeros with a number greater than 0.
SteveL13Asked:
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.

ThomasMcA2Commented:
The query tries to get an average of A/B without checking the value of B first. If B is zero, the query will fail with a "divide by zero" error. Change the query to use NZ or IsNull (I don't remember which one Access uses) to avoid the "divide by zero" problem.
0
PatHartmanCommented:
Count([Participant Nat'l Data Table].[Group Number])  - counts the number of not null values of Group Number in the set.
Count(*)  - counts all the returned records and is more efficient so always use Count(*) unless you really want to count only not null values.

I did a couple.  Make sure you find them all.  I had the expression return 0 if the divisor was null or 0.  You might want to return something different.

IIf(Nz([Mission_PostP],0) = 0, 0, Avg([PREINDmissAVG]/[Mission_PostP])) AS Mission_Change
IIf(Nz([DomesticV_PostP], 0) = 0, 0,  Avg([PreINDAvgDVKOI]/[DomesticV_PostP])) AS DomesticV_Change
IIf(Nz([Satisfaction_PostP], 0) = 0, 0, Avg([EC1_Pre]/[Satisfaction_PostP])) AS Satisfaction_Change

PS - you might want to consider using names that do not include spaces or special characters.  Your query is extremely difficult to read.
0

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
SteveL13Author Commented:
There are 6 total expressions.  I replaced the code with you suggested code (as per the 3 you coded, and still cannot get the query to run.
0
PatHartmanCommented:
When I have a query that has a syntax error that Access isn't clearly identifying, I break it up.  Save the query as it is now.  Then start removing parts of it until you get to something that will run.  Then add the calculations back one at a time.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.