SQL Query

I have submitted this query to the list before, but have since upgraded
to a later version so I lost the command history.

From the below output, could someone tell me how to return rows only
where:

1. If chart_id=10074, return all rows with same trans_id (i.e. trans_id
10088 and 10101)
2. Where amount >=0
3. With transdate between 2002-07-01 and 2002-09-30

accs=# select trans_id, chart_id, amount, transdate from acc_trans;
 trans_id | chart_id |  amount  | transdate
----------+----------+----------+------------
    10088 |    10004 | -2062.12 | 2002-01-03
    10088 |    10037 |     1755 | 2002-01-03
    10088 |    10074 |   307.12 | 2002-01-03
    10088 |    10004 |  2062.12 | 2002-07-03
    10088 |    10002 | -2062.12 | 2002-07-03
    10096 |    10016 |  1169.75 | 2002-12-03
    10096 |    10047 | -1169.75 | 2002-12-03
    10096 |    10002 |  1169.75 | 2002-11-03
    10096 |    10016 | -1169.75 | 2002-11-03
    10098 |    10016 |   283.91 | 2002-12-03
    10098 |    10044 |  -283.91 | 2002-12-03
    10099 |    10016 |    137.6 | 2002-12-03
    10099 |    10045 |   -137.6 | 2002-12-03
    10100 |    10016 |   163.74 | 2002-12-03
    10100 |    10046 |  -163.74 | 2002-12-03
    10101 |    10004 |  -528.75 | 2002-03-20
    10101 |    10037 |      450 | 2002-03-20
    10101 |    10074 |    78.75 | 2002-03-20

Thanks

Scott