Form and table layout questions 1

[ck1999]

I am working on a project that is used for writing and archiving reports.

In one section (or at least for now) there are about 15 comments that can be used in the report. These comments are not choose one but can be anywhere from 0 to 15.

I was going to set up a table with the comments as a memo field.

Here is my thought process. On the form have a check box for each comment in tblcomment then the user can just check which comments apply. I am trying to figure how best to store which options were chosen. and since this database is also going to archive the report I cannot just link to id field in tblcomment since the comments in the tblcomment may later be revised.
How would you recommend me storing which comments apply?

Also the reports can be revised so I would need a way of adding and removing comments as well.

Thank you all in advance

ck1999

 

[MajP]

You are going to need to be a very specific with describing this buisness model. This is what I think I understand.

1)A project can choose from a standard list of comments and maybe there are comments that are unique to only one project. Most of the time these comments do not get modified, but sometimes they do.

2)Once these comments are created a new record needs to be created every time a comment is added, modified, or deleted to keep an audit trail of the comment.

If this is correct then my approach would be something like

tblCommentLibrary
commentLibraryID (pk)
commentDescription
(this is a library of standard comments to use as a starting point)

juncTblProject_Comments
commentProjectID (pk)
commentLibraryID_fk (this is a key that says what the comment started as)
commentDescription
dtmCommentDate
blnDeleted


You can have a checkbox but that is going to require some more coding, because it would be an unbound continous form. There is a way to do it, but takes a little more code.

Lets do it an easier way with an unbound multi select listbox of comments. You click the comments in the listbox and then hit a button to add the comments. This fills in your junction table using an insert query to include the date inserted, and a copy of the original comment.

the key will be this field:
commentLibraryID_fk
This tells you what the comment started as. So lets say the comment choosen was something like
"The building meets XYZ compliance"
which is a standard comment for buildings. Now XYZ.12 comes out and you want to edit the comment. You will need to pop open the comment for editing, which is a prepopulated unbound form. You edit the existing data, but when when you close the form you insert into your junction table with a new date but the same
commentLibraryID_fk
Since these comments are related by this commentLibrary_fk you can roll historical reports based on the report date

This is all based on the assumption I understand what you are asking. I have a similar project where I pull specification out of a document for engineering. These requirements have to be edited so that they can be tested and are understandable, but a trail back to the original statement needs to be maintained to ensure that interpretation is correct.

 

[ck1999]

Thank you MajP. I had not thought about using this method. I will give it a try. I had not thought of using a list box for the options, but I think this would be a much better method since it will allow the user to change comments as needed but give them the "standard" comment as a guide.


Thanks again.

ck1999