Updating records in a table dependant on criteria in another table

[Guest]

Table to update is 20,000 records and grows daily. Would like to update a
field to 0 dependant on a date field falling outside two dates in another
table. This is also dependant on the name corresponding with one of the
fields ie:
Main Table to update
BKNo StartDate ConsName OvAg
1023 01/03/05 Cap 32.6
1024 05/01/05 Gem 42.0
1025 03/01/05 Cap 11.1

Table containing criteria
DateF DateT ConsName
01/02/05 01/02/07 Cap
01/03/05 01/02/06 Gem

Main Table after update
BKNo StartDate ConsName OvAg
1023 01/03/05 Cap 32.6
1024 05/01/05 Gem 0.00
1025 03/01/05 Cap 0.00

Would love a quick and simple way of doing this, i can only think of very
complicated ways and not sure they would work.
Many thanks

 

[Guest]

This is not that hard. We can do that with one append query! Your left
table will be the main table, and the criteria table will be the right table.
Join the tables ConsName. Then in your criteria, [StartDate] < [DateF] or
[StartDate > [DateT]
In Update to, enter 0 for the OVag field.

Warning.... Always start with a select query to see you are getting expected
result fore you change query type to append. That way you wont feel as
stupid as I did the first time I screwed up about 9,000 rows