SQL Query ... How Do I ...

[darrenez]

Below are two tables, one contains the interest rate on a given day.
The columns Settle and Account provide a unique key for the Rate.
The second table Item contains no unique key.

RATES
Settle&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp Account&nbsp &nbsp &nbsp &nbsp Rate
01/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 5
02/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 6
03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 7
04/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 8
01/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 1
02/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 2
03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 3
04/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 4

ITEMS
Today&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp Account&nbsp &nbsp &nbsp &nbsp Amount
01/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 1
03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 3
01/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 10
03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 11
03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 11
05/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp -2

Below is the result of the SQL that I would like
Note Total_Amount is a cumulative to date balance
So [03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 4] is 1+3
Note there is no Rate for 05/06/1998

JOIN
Today&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp Account&nbsp &nbsp &nbsp &nbsp Total_Amount&nbsp &nbsp &nbsp &nbsp Rate
01/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 1&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp 5
03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_A&nbsp &nbsp &nbsp &nbsp 4&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp 7
01/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 10&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp 1
03/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 22&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp 3
05/06/1998&nbsp &nbsp &nbsp &nbsp Pipe_B&nbsp &nbsp &nbsp &nbsp 20&nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp 4

Anybody know who to do this ?
Cheers.

[MisterGoof]

Cant be bothered to write it out in full, but look up SUM and DISTINCT and maybe GROUP BY