Query wont average all required columns

[jackyoung2012]

Hi everyone,



I have a table which has 8 columns of different score categories (service, speed, etc), which can be scored 0-5 each. I am trying to build a query to show the average score for each category, it will show me the average for 6 of the columns but not for the other two! I don't know why this is doing this, as far as I can see they are all set up the same way, but whenever I try to build a summary query using the wizard it doesnt give me the option to calculate averages for two of the columns, any ideas?

Many thanks

Jack

[aytee111]

Try creating it yourself, without using the wizard.

[jackyoung2012]

Just tried that, it appears to have worked, but for some erason the averages for some of the boxes exceeds 5! How can this be possible if the maximum value you can pick is 5?!

[aytee111]

Not sure - can you attach your database (or part thereof)?

[jackyoung2012]

it wont allow me to post it as the filesize is too large, but you can download it from http://dl.dropbox.com/u/1576328/Test...Database.accdb

Any help would be greatly appreciated!

[aytee111]

There are actually some values on the table that are 6. That will put your average > 5.

[jackyoung2012]

I haev just checked and that isnt the same as what I have in my sheet, I dont know why that has 6 in it?

[aytee111]

This is what I am showing:

[jackyoung2012]

I see that, but what query is that from my sheet? Sorry if I am being a bit dumb here, I am teaching myself as I go along!

[aytee111]

I am troubleshooting. I took your query and made my own, I used the same table columns that you used except that I removed the averaging, in order to look at the data prior to messing with it. Opening the table would produce the same results. In development, you must always know the answer before you create something - i.e. during the analysis of what you are trying to create include the results that are expected. So I went back to the beginning, the raw data.

[jackyoung2012]

Ahh right, it looks like what is happening is the query is pulling the values from the primary key auto number rather than the actual score, any idea how to amend the query to pull back the correct values?

[aytee111]

I put them side by side. On the left is the raw data from the Candidate table. On the right is the query with the averages. If you manually create the values you will see that the query is correct - 3+4+5+6 - average = 4.5.