[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

[Jimmy the Hand]

Hi Phil, welcome to TSG Forums!

If I got the picture right then your approach needs to be altered a little bit.
1) The Status field should be removed from Drawing Register table, beacuse the status is, actually, not a property of the drawing, but that of the review. Additionally, placing the same data in two or more different tables/fields is breaking one of the most basic rules of databases.
2) A query should be created, which contains all the necessary fields from Drawing Register table, and also the newest Status from the Review table. The tables can be joined by the name or ID of the drawing, and the query can be used as source for report printing.

Jimmy

[jimr381]

Welcome to the forum.

Can you attach an empty copy of your database so we can take a look at it.