Changing record status from Active to Obsolete and creating a duplicate record

[Basil]

I have records that contain a "revision number" field and a "status" field. I want to be able to change a records status from valid to obsolete but at the same time create a duplicate of the record where I change the revision number to a new number and keep the new record as valid. Hope this makes sense. I'm a relative newbie so if I could do it with a query it would be great but a macro is possible as a last resort.

[isladogs]

You need two queries:
a) Update query to change the Status field to False
b) Append query to add the new 'duplicate' record with the changed revision number value
Having said that, it goes against standard database design principles to duplicate data