I think it would make more sense to put the comment in a related table. Then the relationship can make sure that all relevant records link to the same comment.
I'd give a detailed example but I'm not sure why both records would be liquidated in your example.
Is it because they have the same Product Code?
The Same Color?
The same Color and Code?
The same color, code and season?
The answer you give determines the match fields to use in linking the comments table to your existing table.
The reason they would both be liquidated is because they have the same product code and color. If we are liquidating the women's regular size white then we are liquidating the women's petite size white. And the decision would be seasonally based. So we may be liquidating that product for SS but the fall style we are not. Hard to explain, and maybe liquidate isn't the best example.
I guess I thought by using the self-join I was essentially putting the comment in a related table. Right now I enter a comment on the regular size and then do a search for the other related styles (by product code, color and season) and then copy the comment over. That is basically the same affect I'm trying to accomplish.
Thanks for your help.
Then I'd use this relationship:
CurrentTable::ProductCode = comments::ProductCode AND
CurrentTable::color = comments::color AND
CurrentTable::Season = comments::Season
Then a single comment record made will be linked to all records of the same productcode, color and season. With "allow creation of record via this relationship", enabled, you can simply type a comment into a comment field in the comments table, but placed on your CurrentTable based layout and it will automatically appear with each of the other records of matching code, color and season.
Can you explain why a self-join relationship does not have the same outcome? Just for my learning purposes?
Actually, the self join might produce the same outcome depending on how you design your layout and the relationships. But with a self join relationship, any single record may match to multiple related records and only one would store the comment that was entered. If you use that approach--which seems needlessly complicated, you have to design things in such a way that you are referencing the comment field of exactly the same related record no matter which record of a given product code, color and season is current on your layout.
Thanks for the explanation. I implemented your suggestion of the new table, imported the current comments that I had in the other table and it works like a charm. Thanks!