Query joining two tables with different dates

[bscott]

I'm sure there is a simple solution to this, but being fairly new to Access I
would appreciate some assistance...

I have a query that joins two tables. One table has call details for each day
that an agent works. The other table has details for each call that is graded
for each agent. However, calls can be graded on days that the agent does not
work. When joining the tables, the query will only display calls graded on
dates that the agent works.

This is what I would like to do to correct this: If there was a call graded
on a day that the agent did not work, this data be rolled up to the last day
that they did work.

Current SQL:

SELECT tbl_ScorecardStep1.row_date, tbl_ScorecardStep1.Agent, Sum(tbl_Monitors_ScorecardStep3.SumOfTPE) AS SumOfSumOfTPE, Sum(tbl_Monitors_ScorecardStep3.SumOfTPP) AS SumOfSumOfTPP
FROM tbl_ScorecardStep1 LEFT JOIN tbl_Monitors_ScorecardStep3 ON (tbl_ScorecardStep1.row_date = tbl_Monitors_ScorecardStep3.EVALDATE) AND (tbl_ScorecardStep1.RID = tbl_Monitors_ScorecardStep3.RID)
GROUP BY tbl_ScorecardStep1.row_date, tbl_ScorecardStep1.Agent;

Example of tbl_ScorecardSt ep1:
row_date Agent RID
03-May-10 Frapples, Bob 26152
04-May-10 Frapples, Bob 26152
13-May-10 Frapples, Bob 26152

Example of tbl_Monitors_Sc orecardStep3:
EVALDATE RID SumOfSumOfTPE SumOfSumOfTPP
03-May-10 26152 91 100
07-May-10 26152 76 100
12-May-10 26152 147 200

Current result:
row_date Agent SumOfSumOfTPE SumOfSumOfTPP
3-May-10 Frapples, Bob 91 100
4-May-10 Frapples, Bob
13-May-10 Frapples, Bob

Desired result (5/1 and 5/12 monitor data is rolled up to the last day that
the agent worked:
row_date Agent SumOfSumOfTPE SumOfSumOfTPP
3-May-10 Frapples, Bob 91 100
4-May-10 Frapples, Bob 223 300
13-May-10 Frapples, Bob

Any help pointing me in the right direction is greatly appreciated!