Having a table that would help in documenting student activity on a daily basis

[LisetteG]

I had previously received some great assistance at FM Forums about creating a table for a summer camp that our organization is administering. We would like to add one more feature to this database which would allow for the camp staff to provide daily reports for each camper through three different "evaluating" systems:

1. A ticket system which penalizes each camper for speaking their native language at camp instead of English. Student starts off each day with 5 tickets and must surrender a ticket if caught speaking their native language. Their overall loss affect their whole team.

2. A positive and negative point system-which is used in the classroom to reward good and bad behavior which may also be used to determine class and team overall camp performance.

3. A space where staff members can report specific issues (which will be linked to the negative point aspect in positive and negative point system)

At the end of the day/week/camp we would like to see a summary of each team's losses and gains (positive points) and would also use this information to measure the effectiveness of these systems and track trends in incidents, such as sleeping in class, homework missing, lack of participation, etc.

My first idea is to create a table called "days: which would contain a sort of bank for each camp day as a record (15 days) and then make another table called "camper daily incidents" that would link each student to day for up to three times (for ticket, + points, and - points). The Camper Daily Incident Table would have number fields for tickets lost, plus and negative points given; also a drop down field specifying the incident for which the student was given a negative point, and text field where staff can write in more detail about the negative point incident.

In the end, I hope that I can create a sub-summary report that would allow me sort by team and by student as well in order to see calculated results of the overall ticket and point activity.

Your suggestions would be greatly appreciated as I am a novice FMP user who is always eager to learn. Please let me know if you need further clarifications, I have also included my database's relational diagram so far. Thank you!

[comment]

I don't think you need a "Days" table - it holds no information. Your idea of an Incidents table seems solid. You can summarize the table by week*, day, team, student and type (in any combination/order).

---

(*) To summarize by week, you will need a calculation field such as =

IncidentDate - DayOfWeek ( IncidentDate ) + 1