Update table based on cross-tab query

[giffordpinchot]

Hello World,

I have:

tbl - Trees
fld1 - TreeID
fld2 - year
fld3 - Status
fld4 - Comment
fld5 - UpdateDate

I do a crosstab to look at the status of each tree over the 5-year period (fld 2 can be 1-5):

TRANSFORM Sum(Trees.STATUS) AS SumOfSTATUS
SELECT Trees.TREEid
FROM Trees
GROUP BY Trees.TREEid
PIVOT Trees.YEAR;

Based on various combinations of statuses from year to year I'd like to update the status of previous years. For instance if TREEid=453 was STATUS=8 in YEAR=5 and STATUS=6 in YEAR 4, I'd like to update STATUS for YEAR=5 to 6. Also I'd like to update trees.updatedate with some code (or date, actually) so I can track what I changed.

I'm thinking I need to use the crosstab to look up my information and then do an update query (probably through a module) to change the underlying table. Of course, I'd love a solution that would be easy to test before I actually go ahead and do it.

Also, I can do some VB in modules and am good at interpreting existing code but couldn't write complex ones from scratch myself.

Thanks for your help!

Gifford