Query to keep track of comment history

[REDandBLACKpack]

I am pretty new to using access but I have a database that among other things has two tables. The first, Table A has a bunch of columns with stuff in including counterparty, entity, and comments. I need to have a way for comments to be entered into this table and "backed up" so to speak in another table which would show the counterparty, entity, and a listing of all the comments. Table A would keep the most recent comment.



I have tried using update queries but instead of adding only the rows that have been changed they add everything from Table A. If I try to do something like WHERE TableA.comments <> TableB.comments I get back a message that there are zero rows to update even though I manually changed some of them.

I do not have any primary keys assigned right now and because of the data I am using it would be easier if I did not have one. Is there a way to get this to work for me. I am flexible on how to get it done as long as TableA will show the most recent comment and comments changed in TableA can be updated to another table or query that keeps a history of them.

[TheShabz]

You would need some sort of key to pull it off. You actually wouldn't keep ANY comments in your main table. In a second table, you would have the counterparty, entity, comment, and comment date/time. To get the most recent one, you simply query for max(date/time). each comment would have it's own record so you could query for all or just select comments.

[REDandBLACKpack]

You would need some sort of key to pull it off. You actually wouldn't keep ANY comments in your main table. In a second table, you would have the counterparty, entity, comment, and comment date/time. To get the most recent one, you simply query for max(date/time). each comment would have it's own record so you could query for all or just select comments.

so there is no way to do it without having a primary key?

[TheShabz]

The only reason not to have keys would be because there are multiple records for the same entity. If this is the case, you will come across bigger challenges as you continue to develop your database. Normalization and relationships between entities are integral to a successful database. It's your call. You can either normalize and get relationships with keys going, or dump it all into excel and run Sorts/Lookups.

[REDandBLACKpack]

Okay so if I must use a primary key could you help walk me through the steps to having this comment thing work. I will have multiple records for the same entity but I do not want them grouped together anyway so I can use a key that keeps them seperated.

[TheShabz]

With a comment system, you usually need 3 parts. A commenter, the comment, and what the comment is about. In your case, who are the commenters and what are the comments about?

[REDandBLACKpack]

With a comment system, you usually need 3 parts. A commenter, the comment, and what the comment is about. In your case, who are the commenters and what are the comments about?

The commenters would be whoever is entering in info. There would only be handful of people who can do this. The comments would be about the contracts which is what the database is about. The comments could be things like on 8/2/12 spoke with xxx about xxx and expected to follow up at a particular date. something basic but i would like a history of all of the comments for each contract.

[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).

[REDandBLACKpack]

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).

thank you so much for this. i will try it out tomorrow when i have access to my database. i was not required to track anything by the person who inputs comments but i am assuming it is required for this to work, is that correct

[TheShabz]

It's not necessary by any means. It just makes sense to me that you would want to track who is making comments, for accountability's sake.

[REDandBLACKpack]

It's not necessary by any means. It just makes sense to me that you would want to track who is making comments, for accountability's sake.

yes that makes sense. i will implement it into my database anyway.

[REDandBLACKpack]

This worked great for me. I may need to tweak a couple of things to fit my needs exactly but I am glad that I finally have a comment section working.

I was wondering if you could expand on this part "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)"

I am not sure exactly how to set up the form to do what you are explaining. I would like to be able, if possible, to have one form for my database so that I could add/edit information on my main table that has all of my data (minus contact info and now person info) in it and also on the comment table.

Secondly, and I may have to do this through a query but I would like for that main table to reflect he most recent comment for that contract.

I am assuming that I will probably need to add my contractID primary key to this main table as well.

Thanks again for your help though!