[Buzzdarkmonth]

I'm fairly new to access and have created a database which amongst other things, keeps track of the current status of drawings.
I have a table called "Drawing Register" which contains the information on a list of drawings including drawing number, title, revision and status.

I then have another table called "Review"
In this table I can obtain the title of the drawing from the drawing register, then i manually enter comments on the particular drawing and also it's status (Status being either A, B or C)

I want the "Drawing Register" table to be updated with the status of the drawing (when it is entered into the "Review" table.

The reason for this is I want to print a report from "Drawing Register" showing the current status of each drawing.

Now is the bit I cannot work out.

If I review a drawing which for argument's sake is called "100", I enter the current status (eg Status A)
The "Drawing Register" table updates the "Review Field" to "A"
At a later date, I may want to review drawing "100" again (but create a new record as I don't want to overwrite the previous review record), so if I add a new record in the "Review" table and add new comments for drawing "100", and then I make the status "B", the "Drawing Register" updated the "Review Field" to "B" which is correct, but it also updated the previous record status field (For Drawing 100) to B

I need the status "A" for the earlier record for the review of drawing to remain as it was so that I have a record of that review.

Is this possible?

Can't thank you enough if you can assist with this.

Many thanks,

Phil