Subqueries cannot be used in the expression...

[peng22]

Hello,
I've problem with Access when trying to calculate some stuff.

Let's assume I've the following table

[Name]....[val1]..[val2]
User1........10......2
User1........20......4
User2........30......2
User2........30......2
User2........30......2

Now I would like to use a "group by" for the "name"-column and sum up the val1 and val2.
Moreover I would like to know how many rows were "minimized"

SELECT name, Sum(val1), Sum(val2), Count(name) As amount
FROM tblUser
GROUP BY name

Result:

[Name]....[val1]..[val2]..[amount]
User1........30......6..........2
User2........90......6..........3

Ok now my problem:
I would like to sum up val1 and val2 and then divide with the "amount"

The result should look like:

[Name]....[val1]..[val2]..[amount]....[avg]
User1........30......6..........2.......18
User2........90......6..........3.......32

What I tried (in the access "design view"

Field: avg: ([val1]+[val2])/[amount]
Total: sum

Error message: Subqueries cannot be used in the expression "([val1]+[val2])/[amount]"

Can anyone help me out please?

Thanks in advance

 

[pbaldy]

I doubt you can use the aliases. Try

(Sum(val1) + Sum(val2)) / Count(name)

 

[plog]

You want the average right? Why not just use that function?

Avg([val1]+[val2]) AS AV

 

[peng21]

@plog
You are right!
Such an simple solution... I've totally forgotten that there is something like AVG