Updateable comments in select query

[jw5107]

I have a 1:M scenario...
I have a main table that contains records showing StatusSheet #'s - these StatusSheets have aircraft parts assigned to them. The parts are in a seperate table linked by a common field.
The user needs to be able to update comments about the parts availability for each part assigned to a StatusSheet. One part can be assigned to several different StatusSheets. How can I keep comments seperate if the same part is assigned to different StatusSheets? Do I need to make multiple/duplicate records in the Part table? In order to have seperate comments for each part... I was hopin' not to do this due to using make table queries, and the thought of loosing the comments because of the make table query deleting the table it makes each time its ran.

Just not sure whats the best way to set this up??
Hopefully I'm being clear abou this & it makes sense - kinda tuff to explain... Is there a term for this type of structure or query??

Thanks in advance!!
jw5107

 

[Golom]

Yes. Its called a many-to-many relationship (i.e. A part may appear in many different status sheets; A status sheet may contain many different parts.) You should probably have what's called a link table of the form
[tt]
tblSheetParts
SheetID
PartID
Comment
[/tt]
Then the Comments for the parts on a particular sheet is just

Select SheetID, PartID, Comment
From tblSheetParts
Where SheetID = 1

And All comments for a part is

Select SheetID, PartID, Comment
From tblSheetParts
Where PartID = 'XXX"