When I insert / delete data to two tables, (how) can I keep the links correct automatically?

I have two tables, Measurements and Comments. For each measurement there is 0-n comments. I have the Measurements table like this:ID Resultwhere the ID is an autonumber and result is whatever.The Comments table is like this:ID MeasID Commentwhere the ID is an autonumber and Comment is a string, but that MeasID makes the problem.

Now when I save a result like thisResult: 8Comment: Looks like we got an 8 here.Comment: Doesn't seem too serious.Comment: Keep on, that's ok.

In other words, this could be made like this:Combined Table:ID Result Comment0 2 NULL1 8 Something2 8 Something else3 8 yet another value4 70 what is this comment5 something NULL6 something else comment here7 other value NULL

but clearly it shouldn't be implemented this way, because, after all, we are talking about a relational database.

So now the problem is that when I add a measurement value, while the first table (Measurements) behaves correctly so that there comes a new row likeID Result6 MyNewMeasurementResult

I have no idea where the second table is supposed to draw that MeasID column from. Sure, ID is 5 and comment is whatever, but how can I assign that autonumber value of 6 from Measurements to there? Is the only way to query for the max ID of Measurements table, add 1 to that and insert the row using that +1 (5+1=6) as MeasID?

edit. It just seems like this is mostly used for updating and deleting the data i.e. it still doesn't tell me that when I insert the record with multiple comments, that the comments would know that they are supposed to get the MeasID from the newly created autonumber of their measurement "counterpart".

see this example.. You can use SCOPE_IDENTITY property to know the last isentity value of the corresponding columncreate table testId(id int identity (0,1), name varchar(2))insert into testId VALUES('ch'),('df'), ('aw'),('sd')SELECT SCOPE_IDENTITY() --3SELECT * FROM testID

see this example.. You can use SCOPE_IDENTITY property to know the last isentity value of the corresponding columncreate table testId(id int identity (0,1), name varchar(2))insert into testId VALUES('ch'),('df'), ('aw'),('sd')SELECT SCOPE_IDENTITY() --3SELECT * FROM testID

--Chandu

Ok, I think this solves it. The key thing I was missing was the certainty that I connect the right comments to the right measurements - if two happens at the very same second, this that you posted should have no problems with it. Thanks!