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?
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.
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]));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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.