Using Between Constraint with AVG Function 2

[djs45uk]

Hi All

I've created a ridiculously long SQL statement which probably could be much shorter and more efficient if I was clever enough. Still, it works perfectly, only I now need to add to the end of it a date constraint i.e. BETWEEN #12/25/04# AND #12/25/06# for example.

SELECT Avg(results.resultsQ1) AS AvgOfresultsQ1, Avg(results.resultsQ2) AS AvgOfresultsQ2, Avg(results.resultsQ3) AS AvgOfresultsQ3, Avg(results.resultsQ4) AS AvgOfresultsQ4, Avg(results.resultsQ5) AS AvgOfresultsQ5, Avg(results.resultsQ6) AS AvgOfresultsQ6, Avg(results.resultsQ7) AS AvgOfresultsQ7, Avg(results.resultsQ8) AS AvgOfresultsQ8, Avg(results.resultsQ9) AS AvgOfresultsQ9, Avg(results.resultsQ10) AS AvgOfresultsQ10, Avg(results.resultsQ11) AS AvgOfresultsQ11, Avg(results.resultsQ12) AS AvgOfresultsQ12, Avg(results.resultsQ13) AS AvgOfresultsQ13, Avg(results.resultsQ14) AS AvgOfresultsQ14, Avg(results.resultsQ15) AS AvgOfresultsQ15 FROM results WHERE results.participantCourseID=1

However, I am getting the following error message.

"You tried to execute a query that does not include the specified expression 'respondentCompleted' as part of an aggregate function.

I would be eternally grateful of any help or advice on how to make this work.

Many thanks
Dan

 

[dhookom]

Try:

SELECT Avg(results.resultsQ1) AS AvgOfresultsQ1, Avg(results.resultsQ2) AS AvgOfresultsQ2, Avg(results.resultsQ3) AS AvgOfresultsQ3, Avg(results.resultsQ4) AS AvgOfresultsQ4, Avg(results.resultsQ5) AS AvgOfresultsQ5, Avg(results.resultsQ6) AS AvgOfresultsQ6, Avg(results.resultsQ7) AS AvgOfresultsQ7, Avg(results.resultsQ8) AS AvgOfresultsQ8, Avg(results.resultsQ9) AS AvgOfresultsQ9, Avg(results.resultsQ10) AS AvgOfresultsQ10, Avg(results.resultsQ11) AS AvgOfresultsQ11, Avg(results.resultsQ12) AS AvgOfresultsQ12, Avg(results.resultsQ13) AS AvgOfresultsQ13, Avg(results.resultsQ14) AS AvgOfresultsQ14, Avg(results.resultsQ15) AS AvgOfresultsQ15 
FROM results 
WHERE results.participantCourseID=1
AND RespondentCompleted BETWEEN #12/25/04# AND #12/25/06#

You might want to consider normalizing your surveys in the future. Ask if you have any questions...


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Golom]

The SQL that you've posted does not contain a reference to respondentCompleted nor does it have the BETWEEN clause to which you refer, so I'm assuming that this is not the SQL that's raising the error.

If that's the case, can you post the SQL that is having the problem?

 

[djs45uk]

Sorry for not making myself clear the first time. I should have told you I have tried the following (thanks dhookom anyway):

SELECT Avg(results.resultsQ1) AS AvgOfresultsQ1, Avg(results.resultsQ2) AS AvgOfresultsQ2, Avg(results.resultsQ3) AS AvgOfresultsQ3, Avg(results.resultsQ4) AS AvgOfresultsQ4, Avg(results.resultsQ5) AS AvgOfresultsQ5, Avg(results.resultsQ6) AS AvgOfresultsQ6, Avg(results.resultsQ7) AS AvgOfresultsQ7, Avg(results.resultsQ8) AS AvgOfresultsQ8, Avg(results.resultsQ9) AS AvgOfresultsQ9, Avg(results.resultsQ10) AS AvgOfresultsQ10, Avg(results.resultsQ11) AS AvgOfresultsQ11, Avg(results.resultsQ12) AS AvgOfresultsQ12, Avg(results.resultsQ13) AS AvgOfresultsQ13, Avg(results.resultsQ14) AS AvgOfresultsQ14, Avg(results.resultsQ15) AS AvgOfresultsQ15 
FROM results 
WHERE results.participantCourseID=1
AND RespondentCompleted BETWEEN #12/25/04# AND #12/25/06#

But this produces the following error:

"You tried to execute a query that does not include the specified expression 'respondentCompleted' as part of an aggregate function.

FYI, it works perfectly without the BETWEEN condition at the end.

Any help is greatly appreciated!

Daniel

 

[dhookom]

I can't see any reason why the SQL your provided would result in that error message. I would try copy the SQL and paste it into a new, blank query. If you get that error you stated, it would be consistent if you removed the "AND RespondentCompleted BETWEEN #12/25/04# AND #12/25/06#" part, you would get a similar error with the "results.participantCourseID=1".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Golom]

Check "Tools / Options / Tables/Queries / Output All Fields" and make sure that it is unchecked.

 

[djs45uk]

I am so very grateful to both of you for all your help!

I copied the SQL that I had been using into a completely new query as suggested and it works.

I also checked that the option "Output All Fields" is unticked which it was.

Many thanks again

Daniel