Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2015
    Posts
    6

    Unanswered: MS Access 2007-10 - creating student roster database and data entry form

    Hello, DBF, it's great to be in the company of so many database experts and volunteers.

    I work in education management and have been tasked with creating a student roster database. I took an Excel file given to me by a coworker as a starting point.

    My goal is to create a linked database where my coworker can quickly enter data in the form, have it recorded in the table, and also have an easily printable form for each class of students we have.

    Here is the current look of the database.

    Click image for larger version. 

Name:	database1.jpg 
Views:	5 
Size:	232.9 KB 
ID:	16612

    Here is the current look of the data entry and printable form in design view.

    Click image for larger version. 

Name:	formdesign1.jpg 
Views:	2 
Size:	303.6 KB 
ID:	16613

    Here are pictures of the current output.

    Click image for larger version. 

Name:	current output1.jpg 
Views:	4 
Size:	154.9 KB 
ID:	16614
    Click image for larger version. 

Name:	current output2.jpg 
Views:	2 
Size:	153.4 KB 
ID:	16608
    Click image for larger version. 

Name:	current output3.jpg 
Views:	2 
Size:	157.1 KB 
ID:	16609

    Here are mock-ups of the desired output.

    Click image for larger version. 

Name:	desired output 1.jpg 
Views:	2 
Size:	151.6 KB 
ID:	16610
    Click image for larger version. 

Name:	desired output2.jpg 
Views:	2 
Size:	147.8 KB 
ID:	16611

    Hopefully the pictures convey what I am trying to do.

    Where I'd like to go from here would be based on...

    I have seen some other webpages about databases which suggest creating a separate database with just student info. If I create a database that has just the student info, how exactly do I link that with what classroom (labeled CR) the student is put into?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first things first, in a relational database such as Access its always a good idea to normalise your design.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2015
    Posts
    6
    Quote Originally Posted by healdem View Post
    first things first, in a relational database such as Access its always a good idea to normalise your design.
    I've seen that referenced elsewhere and have started reading about it. It looks like the starting point is to get the student data in place into a separate table first - referencing the ID number and name. It seems, from what I am reading, is that normalization removes repeated data.

    What is still befuddling me is how to tie that in with their roster placement. Students with ID number 157412, 177851, etc. are all in class 1, for instance. But how to link that repeating data piece to those students and get them all on the same roster page? It is mysterious.

    Question: Does class 1 therefore need its own table?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why would a class need a separate table?
    what would the implications being if you were to have a separate table for each class
    what would happen if la la land (the management of the organisation) decide to introduce a new class

    understand normalisation
    do it on your current model

    when you have completed normalisation (or more accurately you have pushed it as far as you need to ) then look at implementation, understand intersection tables
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2015
    Posts
    6
    OK, I ditched the idea of separate tables for the classes.

    I went to my original datasheet and put it into pivot view. I then put in the columns according to how I need it sorted (basically by CR = classroom, then displaying the attendant info for those students in that classroom). Each of those CR groupings is exactly what I want to put onto its own form.

    Click image for larger version. 

Name:	studentpivottable.jpg 
Views:	3 
Size:	161.4 KB 
ID:	16615

  6. #6
    Join Date
    Nov 2015
    Posts
    6
    Another approach: Create a form based off of my Faculty table.

    First, here is the Faculty table.

    Click image for larger version. 

Name:	facultytable.jpg 
Views:	2 
Size:	182.1 KB 
ID:	16617

    Here is the Student Info table I separated off from my original datasheet.

    Click image for larger version. 

Name:	studentinfotable.jpg 
Views:	0 
Size:	241.4 KB 
ID:	16619

    This is the table that tells us which student goes in which class.

    Click image for larger version. 

Name:	classdesignationtable.jpg 
Views:	0 
Size:	188.5 KB 
ID:	16620

    And here is the corresponding Faculty form. This creates a separate record for each class, not each student, which is what I need in this situation. (Each faculty member corresponds to only one class.) However, I am still not able to get only the student information for the corresponding class onto that class record.

    Click image for larger version. 

Name:	facultyform.jpg 
Views:	2 
Size:	181.8 KB 
ID:	16618
    Last edited by StudentSuccess1; 11-03-15 at 04:06.

Posting Permissions

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