Acess SQL problem 1

[shaddow]

I have 2 tables (1 to many) and i made a querry that doesnt work as it should be. Not shure of all restrictions of the Access SQL.

select client, valoare, sum(suma) as platit , (valoare - platit) as rest ... and the rest

The problem i have it's that it doesnt recognize platit from the (valoare - platit) as rest, as well from the where clause...

It's this a Access restriction?

________
George, M

 

[KenReay]

Hi

yes you have to say:

WHERE (valoare - platit) = whatever

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[shaddow]

It doesnt work, it always asks (it prompts) for the aliases values used in where and as i told before even if i want to make another alias. "sum(suma) as platit , (valoare - platit) as rest"



I just cant understand why it acts like this, not to mention that i never had problems with anyother SQL querry.(SQL,MySql etc.)

________
George, M

 

[flaviooooo]

simply use (valoare - sum(suma)) as rest instead

 

[shaddow]

Yes did that to, but now the problem is that i need that rest to be like:

... WHERE rest>0 - doesnt work
... WHERE (valoare - sum(suma)) doesnt work, cuz WHERE clause doesnt accept sum(suma) something about agregate functions.


________
George, M

 

[r937]

use HAVING, not WHERE, for conditions involving aggregates

be careful with your GROUP BY, too

rudy

 

[shaddow]

r937, you are right.
I needed somone to wake me up.
Thanx again, that was the damn problem, forgot completely about having in Access.


________
George, M