show only max date

[mike]

Any help with this would be great. I have a table,
tblPlans, that keeps track of what my financial plan is
for each territory in my organization. It looks like this:

AccountNumber MonthID Year Plan Territory
0000020527 1 2004 300 Houston
0000020527 2 2004 200 Houston
0000020527 3 2004 100 Houston
0000020527 4 2004 500 Houston
0000020527 5 2004 600 Houston
0000020527 6 2004 400 Houston
0000020527 7 2004 600 Houston
etc..

The problem is that accounts sometimes transfer (always on
the first of the month), so I'm keeping a list of account
transfers in a table, tblAccountTransfers, shown below.

AccountNumber Territory EffectiveDate
0000020527 El Paso 2/1/2004
0000020527 Dallas 6/1/2004

From this I create a query, qryAccountTransfers, that
creates an effective month and year:

AccountNumber Territory EffectiveMonth EffectiveYear
0000020527 El Paso 2 2004
0000020527 Dallas 6 2004

What I'd like to do is use an update query that looks up
the account number, month, and year in qryAccountTransfers
and changes the territory in tblPlans accordingly. In this
case, when I entered El Paso in my table and ran the
update query, the Territory for account 0000020527 would
have been updated to El Paso for every month equal to or
greater than the Effective Date, or, months 2-12. When I
added Dallas, the same thing would have happened except
Dallas would update El Paso to Dallas for months 6-12.

AccountNumber MonthID Year Plan Territory
0000020527 1 2004 300 Houston
0000020527 2 2004 200 El Paso
0000020527 3 2004 100 El Paso
0000020527 4 2004 500 El Paso
0000020527 5 2004 600 El Paso
0000020527 6 2004 400 Dallas
0000020527 7 2004 600 Dallas
etc..

The problem I'm running into is figuring out how to tell
my update query to ONLY LOOKUP THE MOST RECENT ENTRY in
qryAccountTransfers, and only update those months that are
greater than or equal to that month for that account. I'm
stuck. Any thoughts would be great!! Thanks.

 

[chris]

You will be lucky to find a query to do what you need if
you want to process all entries in your
tblAccountTransfers in one hit.

If you only want to process an entry as you store it, that
would be easier.

To do the lot in one hit I would suggest a function to
step through all entries in tblAccountTransfers in
ascending date order and update all entries in tblPlans
where account numbers match and date is equal or greater
(later changes would overwrite earlier changes).