Access 2007 advice needed. Student Attendance Sheet

[ClareLou80]

I have been working on a database that will track the following:

Students details
Classes provided
Dates of classes

I keep hitting brick walls. I can either get it so i can see all of the students in a particular class OR i can get it so i can see the specific dates of a class. But failing to combine all 3 things.

What i need is for there to be a set group of students attending a class on a set group of dates, with a yes/no column at the end to show if that student attended on that date. The students attending each class will remain the same for the duration of the course and the dates will be pre-set at the start of term and not change. So:

1 student can attend many classes
1 class can have many dates
1 class can have many students

I've made several databases with varying tables in it! But would like advice on where to go next. I have searched net (but none of the templates i found fit my need), i've enrolled on a course (but even the tutor is struggling to come up with an answer), so now i am turning to you to ask the experts!!! Advice please!

 

[Mr. B]

Getting the structure of your database designed correctly is (as you have already discovered) the most critical part of creating any database.

I would suggest that you might want to think about the design of your database in terms of exactly how things are in real life. Identify each thing that you are needing to track. Each group of information you need to track should be in one table. In most situations, each table should have a Primary Key field or a combination of fields that create the primary key (I prefer to use the AutoNumber field as the primary key).

In your case, one type is information is abou students. So you would need a table for information about each student. Another type if information would be about Classes. Another would be the schedule of classes that would be related to the Classes table. Now, because you have already discovered that you have "many-to-many" situations, you need a table that can bring all of these together. You could call this table "tblStudentCLassesLinks" becasue that is exactly what it will do.

This table would have a primary key field, and at least two "foreign Key" fields. A foreign key field is a field where the value from the Primary Key of another table can be stored, linking the data from both tables together (there are multiple types of ways tables can be "joined" or related to each other). In your case at a minimum you would need fields like:

StudentClassID - AutoNumber - Primary Key
StudentID - Number (Long Integer)
ClassID - Number (Long Integer)

With this table you have a way of bringing all of the needed data together.

Hope this helps.

 

[ClareLou80]

Thanks Mr B! I will give it a go and see what happens. I understand what you are saying. I need a junction table...!

 

[ClareLou80]

Ok...i've attempted this, played around with the tables and forms, and its still not quite where i want it

I want to be able to go to a class, for example, Golf, and then enter all of the dates that golf will take place for the term. These dates would then be fixed to this class.

I also want to be able to go to a Student and view all of the classes they are enrolled on.

Most importantly, I want to be able to view, by Class, all of the students enrolled on that class AND the dates (set from the earlier example where i had put them in against the class), with the final field as a yes/no for if that student attended the class.

Is this possible? Advice please. I've attached my database so far so you can see where i'm at.

 

[Mr. B]

Ok, now that you want to have multiple classes with multiple scheduled times for that class, you now have yet another many-to-many relationship. For this you would need to have another table that will link the class with a scheduled time for the class.

Then to be able to link the student to a class available at one specific time, you would use the ID field from the new table in the original many-to-many table instead of using the Class ID.

Again, just keep thinking in terms of how things are related to each other in real life and model that with your database.

 

[ClareLou80]

I'm starting to feel like an idiot!!!

Ok, so i understand the principal of what you are saying (i think). I need groups and definite questions:

If i want to know which student is in which class, i need:

tblClass+tblStudent LINKED via tblJunctionClassStudent. This should tell me who/which student is in which class.

If i want to know when each class will take place then I need

tblClass+tbl_Date LINKED via tblJunctionClass_Date (where ClassID and _DateD are FKs). This should then tell me when each class session is taking place.

I then need to link the junction tables by their PKs? So tblJunctionClassStudent+tblJunctionClass_Date should tell me which student is in which class on which day??? And i assume i can add the attended yes/no field to this new Junction table, which i think would make sense to call tblJunctionSchedule

I'm trying to do this, but it's all getting pretty confusing...sorry! For example, when i put in tblClasses Golf and then add a student to the class, it's not pulling through. In tblClass Golf show aa as attending, but in tblStudent, aa does not show as attending Golf?

 

[Mr. B]

Don't feel bad, uJust so you know, the learning curve that you are expereiencing is the same one that all of us have, at sometime, also experienced.
Yes, it would be appropriate to have the Attended (Yes/No) field to be in the "class schedule" Join table that has the ability to link each scheduled session of a class with specific students.

When you have this join setup correctly you will be able to extract a list of students that attended (or did not attend) one scheduled class.

I may have confused you when I started talking about the need for another many-to-many relationship. You may actually on need one join table.

I am rather busy at the moment and have a deadline that I must meet but when I get a few minutes, I will try to take a quick look at the attached DB and get back to you.

 

[ClareLou80]

Thank you for your support and kind words and even messaging me despite your deadline! It is really appreciated. I look forward to hearing from you.

 

[Mr. B]

I finally got a few minutes that I could take a look at your database.

I make some significant changes to the structure of your tables and the fields in them.

I have attached the modified version of the database. I am sure you will have questions so please just ask and I or someone here on the forums will be happy to try to help.

 

[ClareLou80]

Thanks for taking the time to look over this. I've had a play with it, and i'm wondering if what i need now comes down to queries and forms?

The thing is, it still does not show me an individual student and the classes they are enrolled on and i still have to enter the student group repeatedly to have them show up against a class.

This is so frustrating. I'm sure it's just me and i'm not getting it as opposed to anything else

I've attached a spreadsheet to show how i would like the information to be shown in the database:

(please excuse how crude the spreadsheet looks! It is only to help illustrate what i want)

 

[ClareLou80]

For some strange reason, the spreadsheet will not attach, so i will write what i mean below:

So, if i want to know Class and students, then i would want it to look like:

GOLF
John
Mary
Simon

If i want to view the classes 1 student is taking:

JOHN
Golf
Yoga
Boxing

If i want to know attendance:
GOLF 11/09/11 17/09/11 24/09/11
John yes/no yes/no yes/no
Mary yes/no yes/no yes/no
Simon yes/no yes/no yes/no