[deltam]

I need to do a single query that updates a date field in one table... then adds a new record in another table.

Here's the table/field that I need to update and where the primary key is pulled from to Add a New record in other table:

tTenant
ten_id (the ID field/primary key)
ten_RentAppliedMonth (is a date field in format #m/d/yyyy# or NULL)
ten_moved_date (is a date field in format #m/d/yyyy# or NULL)
ten_rent (currency)
ten_newtenantflag (true/false flag)

I need to do pull a query on tTenant where
ten_RentAppliedMonth is NULL OR <= #7/1/2004#
AND ten_moved_date is NULL
AND ten_newtenantflag is FALSE

then update ten_RentAppliedMonth to #7/1/2004#

with the ten_id, and ten_rent values from above, i need to create a new record in other table.

UPDATE tTenant, tTransactions
SET tTransactions.tran_TenantID = [tTenant.ten_id],
tTransactions.tran_LineBalance = [tTenant.ten_rent],
tTransactions.tran_TransactionDate = #7/1/2004#,
tTransactions.tran_Rent = [tTenant.ten_rent],
tTransactions.tran_TransactionMemo = "Rent posted by BATCH",
tTransactions.tran_Initials = "BATCH",
tTenant.ten_RentAppliedMonth = #7/1/2004#
WHERE (((tTenant.ten_RentAppliedMonth) Is Null)
AND ((tTenant.ten_newtenantflag)=False)
AND ((tTenant.ten_moved_date) Is Null))
OR (((tTenant.ten_RentAppliedMonth)<#7/1/2004#))

Can this all be done in one query?

TIA!