I need a database that will compare a list of students to the assignments they need to complete.
I want to check off when they have completed the assignment, and be able to run a report that lists all the things they did. I know this is a simple problem for most of you, but I can’t figure out how to set it up.
Any suggestions?
Thanks, CM
Start with a name or other identifying field for each student, and then line item fields for assignments and status, running from 1 to however many there are. So:
Name, Assignment1, Status1, Assignment2, Status2, etc. You might want the StatusΩ fields to have choices to indicate that they were assigned, in progress, or completed. If all the students get the same assignments in the same order, then all you would really need is the Status.
Or you could have instead separate line items for the date of the assignment, the date of completion, the grade, and notes for each student’s assignment. You are only limited by your imagination.
Read about line items in Help. There are a lot of tools to help you with them.
CM, as you can imagine - in fact, “imagine” is a keyword here - comparing can take several forms. You can look at a student record and see a list of assignments with some “completed” checkbox, or you can select an assignment and see an associated list of students who have that assignment, along with their completed indicator.
Compare could also mean seeing a sorted list of students where rank is based on the number of completed assignments.
My suggestion is, before you begin, to sketch on paper what you want to see on the screen. Then make a list of the desired functionalities; add/delete assignments, complete/incomplete indicator, perhaps the date of completion, etc. Then you work towards those goals - like building a building one floor at a time.
One feature that was more complex than users - in the olden days - realized was having the ability to both see the individual student’s assignments and also see all the students who had a specific assignment. In other words, “These assignments belong to this student.” and “These students belong to this assignment.” Accomplishing that would require a third file with student/assignment parings. From that file, you could select all the assignments for a student or all the students for an assignment.
Your situation adds a third element - completed or not. This is a new world for me but I imagine a third file that holds studentID, AssignmentID, and Completed status.
But one doesn’t want to go too far down the “How” road without a more specific “What” map.
The difference between my vision and Bruce’s is he seems to use the omega feature that is often used for items in some invoice designs. The student is the invoice itself and the line items (products) are the assignments. The “price” is the completion status of those assignments. That style requires you to predetermine the maximum number of assignments ahead of time. Nothing wrong with that; it’s one way to go. My vision allows just adding students, as many as you want, and just adding assignments, as many as you want. The databases grow in records, not in fields.
This is an important concept. When people would migrate from a spreadsheet to Panorama, they were used to seeing fixed items, categories for costs, and expenses, vertically down the left side of the worksheet. The monthly amounts of those items were shown across the top of the worksheet. So they’d design their panorama datasheet to look the same way. Problem was, they had to constantly go into the design and add new fields as time continued. It was better to have the categories as fields and add records for the dates. That way the information could grow without having to constantly change the design. Sometimes I’d see a database with nearly 100 fields - and growing - when it could have only 15 fields and 100 records. That style makes a much happier database.
So think about how you want this to grow over time. You want to be adding records, not adding fields.
That’s my story and I’m sticking to it.
PS: Yes, I skipped over all the “This is how you relate the student/assignment/completion status.” That’s because it’s a little work and I’m not sure you are asking to see it both ways (from the student’s point of view and the assignment’s point of view). If you are talking about 20 students and 10 assignments, and you don’t care about looking back from the assignment to the students, Bruce’s suggestions are much simpler to implement.
Thanks, guys. You gave me some things to work on.
CM
leads to an article about designing a relational database. It targets MySQL but the description of setting up files (tables), fields, and relationships helps “get your mind right” for the task at hand. It also introduces a tool, LucidChart, for diagramming relationships. It’s a three-part article and the further you go, the more MySQL-isque it becomes. But the verbs they use will assist in recognizing what needs to be done in PanX to accomplish the same thing - faster and with more polish.
When I started this post, I mentioned that the many-to-many relationship between students and assignments would no longer need that third file linking file - as was used in yesteryear. But the above link’s example of students taking many courses and courses having many students still uses a third “Takes_Courses” file.
For your task, it’s just students and assignments. The structure is the same, field names are just different.