Results 1 to 6 of 6
  1. #1
    Join Date
    May 2010
    Posts
    14

    Unanswered: Creating a 'comments' section for each record which records user details

    Hey,

    I'd like to put a comments section in each of my records which allows the database users (a trainer) to insert comments on each record (a student). However when they input this comment i'd like it to record the Date, Comment and Trainer Name (could even just ask with a msg box).

    Since there will be quite a few entries by all different users, I'm thinking of using a separate table (which holds all the comments) - (many to many relationship) and form to do this input, linked by a Student ID. Then have each comment its own record (including date ..etc), linked by the ID. However i'm not too sure about this since there will be likely over 30 comment entries per student, and for the database to search through ALL comments of ALL students (over 2000 students) may be quite slow. Correct me if i'm wrong though..

    I'd also like these comments to be locked, so they could only be deleted by a password or something else secure like that.

    Any help would be much appreciated!
    Last edited by yakkib; 05-26-10 at 22:55.

  2. #2
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    For any given student and or trainer the impost of 30 records is not great. However, I suppose that what you are thinking about is a report for, say, 100 students which sounds much worse. The answer I think is to explicitly declare the relationships between students, comments and trainers (see menu Tools/Relationships), ensuring that each record has a simple primary key ( I prefer long numeric integers created by autonumber). This will ensure that the underlying indexes will be kept up to date. Then you can create a couple of queries, one to extract all of the comments for a specific student and another to extract all of the comments by a specific trainer. These tools will allow cachng of responses and maintain a decent performance rate. In other words, the more Access knows before you start, the faster it will run.

  3. #3
    Join Date
    May 2010
    Posts
    14
    Jim, Thanks for the response.

    I've just recently found out that these comments can go up to about 50 per student. So yes, its not great to think that over 100 000 comments is being kept in one table. I don't think that the trainer comments nor the date of comment will ever need to be searched. Just going to basically report on all these fields. Was thinking if there was a way to create a comments 'memo' field on each student and have it auto add the date and username to the comment when entered...

  4. #4
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by yakkib View Post
    Jim, Thanks for the response.

    I've just recently found out that these comments can go up to about 50 per student. So yes, its not great to think that over 100 000 comments is being kept in one table. I don't think that the trainer comments nor the date of comment will ever need to be searched. Just going to basically report on all these fields. Was thinking if there was a way to create a comments 'memo' field on each student and have it auto add the date and username to the comment when entered...
    100 000 comment records is not a lot. Over 1 million might start getting close to beimg consider a lot.


    I would highly urge you to avoid the temptation to use one memo field to hold all the comments.


    Using a separate table with one comment per record is the only way I would recommend you handle this.

    I would bet that in the future you will need a report that shows all the comments for a single trainer.

    What if you need all the comments done in a time period?

    There are reasonable requests that your design should be able to handle.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    May 2010
    Posts
    14
    Thanks alot guys! Will put this in place

  6. #6
    Join Date
    May 2010
    Posts
    601

    Thumbs up

    You're Welcome.

    Glad we could assist.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •