Can you explain what you want to do with these comments in more detail? Perhaps with an example of how you want to make this work?
I don't understand why you have this set up as you can simply click or tab into a comment field and enter a comment. I don't get the process of "saving" nor why both tables have comment fields yet are linked by the Year. Do you want one comment to apply to all students for the year?
the comments change every so often and I cannot have an ever increasing numbered list of comments as this will be unmanageable for the teachers
You might want to consider a related table linked to students that stores all comments ever posted about that student. A portal can display those comments with them sorted so that the most recently added comments are listed first. That should make the comments manageable, but also provide the ability to review all past comments posted about a given student.
Thanks for the reply.
Basically this is for use in a foreign language school. The teacher chooses the English comment but the French version of the comment is entered into the reports etc. sent to the student or parents (as this is their local language).
There are around 600 students.
I haven't used portals at all so I must have a read.
Hope this helps.
Yes but why enter a comment into a table linked to all students by year? You don't want to enter the same comment for all students for that year do you?
Are you not recording a different comment for each student? Perhaps one or two comments for each student per year?
It could be up to around 10 comments per student per year.
The reason I want to keep the comments per student per year is they may be there for more than one year and we need to be able to go back and look.
There is also other stuff on the report that's not an issue to store too.
Ok, but why do you have comments in both tables?
Here's what I recommend from what may be an incorrect understanding of what you need:
Students::__pkStudentID = Comments::_fkStudentID
Fields in the Comments table:
Date (auto-enters the creation date), Comment ( text) , _fkStudentID (number) and maybe a cYear field defined as Year ( Date ).
You can place a portal to Comments on your Students table and sort the portal by Date in Descending order so that the most recent comments appear at the top of the portal. At your option, you can add a portal filter that filters the comments by year to show either only the comments for the current year or only the comments for a specified year. With a portal that sorts the most recently added comments to the top, you may want to add a button with a script for adding new portal records so that you can click a button to put a new blank record at the top of the portal in order to then enter data into the comment field in that new blank record to record a new note.
And now that I've posted this, I realize that if you have a traditional school year that starts in the latter half of one year and ends in the first half of the following year, you may want to define cYear with a more sophisticated calculation to return the same year for your entire school year rather than the calendar year, but the basic principles described here will be the same.
Thank you so much for your time, I will look at this now. Going to do some playing around with Portals so I understand.