Originally Posted by
TheShabz
So you would have 3 tables:
tblPerson
PersonID
personName
etc
tblContract
ContractID
ContractName
etc
tblNotes
NoteID
NoteBody
noteDate (or time, depending on your needs)
PersonID
ContractID
Key:
Underlined - Table Name
Bold - Primary Key
Italics - Foreign Key
What you would do is populate the first two tables with data that your users will not be allowed to adjust. You then create a form that will populate your Notes table (note that the relationship create a many to many relationship between Person and Contract) with the comments and any relevant info for the comment. Once you have this, you can query for all comments based on whatever criteria you need (commenter, the contract it is regarding, or any time periods).
Now lets say you need to pull all comments regarding contractID XYZ:
SELECT tblPerson.PersonName, tblNotes.*
FROM tblPerson INNER JOIN tblNotes ON tblPerson.PersonID = tblNotes.NoteID
WHERE tblNotes.ContractID = "XYZ"
ORDER BY tblNotes.NoteDate Desc;
That will give you all the notes entered for Contract XYZ, the person who entered the note, and will sort it in descending chronological order (newest first).