MS ACCESS/SQL Question

[JJChicken]

Hoping you geniuses can help me out.

I have a database

Account............Description..................Dollar_Amount
1.........................Transaction A................500
2.........................Transaction B................300
2.........................Transaction C................800
1.........................Transaction D................10
1.........................Transaction E................1000

I want build a query that returns all rows where

absolute value(dollar_amount) > 10% absolute value(sum total for the account)

E.g. In the above, it will output:

1.........................Transaction A................500
2.........................Transaction B................300
2.........................Transaction C................800
1.........................Transaction E................1000

Transaction D will get left out because 10 < 10% of 1510 (sum total for account 1).

Does anyone know how to achieve this? Do I need to resort to expressions?

 

[BoberFett]

I'd just use a simple subquery.

select * from table t where (dollar_amount * 10) >= (select sum(dollar_amount) from table where account = t.account)

 

[Cogman]

I'd just use a simple subquery.

select * from table t where (dollar_amount * 10) >= (select sum(dollar_amount) from table where account = t.account)

That, and stop using access (unless you don't value data integrity 😛)

 

[JJChicken]

Thanks guys!