none
Help with entry forms and creating tables. RRS feed

  • Question

  • I am creating a student mark database for a teacher. There should be an entry form to create an assignment and once the assignment is created he should be able to quickly input marks for each student (possibly going down a table using the number pad and arrow keys unless there is a better way). Is there a way to create a new table for the assignment (with student names in the rows and different assessment criteria in the columns) each time he creates an assignment or should this be done another way?

    Monday, April 3, 2017 10:42 PM

All replies

  • Why would you need to create a new table?

    You should have an Class table, an Assignments table (linked to the Class table), a Student table (linked to the Class table)

    So you add a new assignment to a class it automatically knows which students it applies to.  Then you could automatically generate a records for each student in a Grades table (Linked to the Assignments table).


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, April 3, 2017 10:53 PM
  • Hi, Not sure if you're new to database design but before you can think about creating the user interface, you'll need to make sure you have a good table structure. Can you post your table structure to give us a better idea about your database? Thanks.
    Monday, April 3, 2017 10:54 PM
  • Each assignment has four possible criteria (knowledge, thinking, communication, application) and not all four criterias need to be assessed for a given assignment. The assignment entry would have: assignment name, four criterias checkboxes (K, T, C, A), what each criteria is out of (for example, /10 for K, /20 for T, /15 for A, and nothing for C if it is not checkboxed), and a weighting for the assessment (mark multiplier that allows a test to be valued higher than a quiz for example). The marks for all the criteria should be total and converted to a total percentage for that assignment. I'm not sure how to fit all of this information for each assignment in one table. 

    Monday, April 3, 2017 11:11 PM
  • Hi, yeah im new to database design and creating databases. I briefly learnt about using MS Access in the past (know basic tools such as entry forms, reports, queries) but never used MS Access to create a fully working database. By table structure I assume you mean the tables I am going to include in my database and how they will be linked? The only tables I know that will be included are Students (name, gender, student #, class) and Classes (four diff classes). I'm unsure as to how to properly link the creation of new assignments throughout the year. 

    EDIT: An assignments table would probably also be included but I'm sure what the best way to input student marks for each assignment would be (with the the mark broken down into the four (or less) criteria). 

    • Marked as answer by Perpenderp Monday, April 3, 2017 11:16 PM
    • Unmarked as answer by Perpenderp Monday, April 3, 2017 11:16 PM
    • Edited by Perpenderp Monday, April 3, 2017 11:19 PM
    Monday, April 3, 2017 11:16 PM
  • I am creating a student mark database for a teacher. There should be an entry form to create an assignment and once the assignment is created he should be able to quickly input marks for each student (possibly going down a table using the number pad and arrow keys unless there is a better way).

    The tables I plan on including are a Classes table (four diff. classes) and Students table (name, gender, student #, class).

    I'm unsure how to incorporate an assignments table. Each assignment has four possible criteria (knowledge, thinking, communication, application) and not all four criterias need to be assessed for a given assignment. The assignment entry would have: assignment name, four criteria check boxes (K, T, C, A), what each criteria is out of (for example, /10 for K, /20 for T, /15 for A, and nothing for C if it is not check boxed), and a weighting for the assessment (mark multiplier that allows a test to be valued higher than a quiz for example). The marks for all the criteria should be total and converted to a total percentage for that assignment. I'm not sure how to fit all of this information for each assignment in one table. Can a new table for each assignment be created?


    Monday, April 3, 2017 11:42 PM
  • Hi Perpenderp,

    As Daniel  Pineault already suggested you, you can create 3 tables for student, assignment and class.

    then you can create relationships between them.

    then you already have a data for students and class in relevant tables.

    so now you need to create table for Assignment.

    you can create table like below.

    Assignment Table:------
    Assignment_Id Autonumber Primary key,
    Student_Id number,
    Class_Id number,
    Assignment_Type Short Text, 
    Assignment_Topic Short Text,
    Assignment_Creation_Date Date/Time,
    Assignment_Submission_Date Date/Time,
    Points number,
    Remarks Long Text
    

    you can Add / Remove fields as per your requirement.

    then you can create form , where you can make an entry for these records and generate report to display the data.

    Regards

    Deepak

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 4, 2017 12:45 AM
    Moderator
  • Hi Deepak,

    If I were to create an assignments table, each time a new assignment wants to be added, would a record appear in this table after an entry form is filled out? The entry form would have assignment name, weighting, criterias assessed (knowledge, thinking, communication, application), and maximum marks for each criteria (e.g. /10 knowledge, /20 thinking, etc.). With the assignment information stored in this table, how would student information be inputted?

    The necessary columns for a table like this would be student ID, student name, assignment ID, knowledge, thinking, communication, application, and (calculated) total score. Ideally, i'd like for these to be a separate table holding student marks for each assignment (would this be done using a report?). Is there a better way to store the students marks for each assignment?

    Tuesday, April 4, 2017 1:00 AM
  • Hi,

    Is this a continuation of this other thread? If so, I suggest you keep all discussions in one thread to avoid confusion or redundant discussions.

    Just my 2 cents...

    Tuesday, April 4, 2017 1:03 AM
  • Hi Perpenderp,

    you had asked,"If I were to create an assignments table, each time a new assignment wants to be added, would a record appear in this table after an entry form is filled out?"

    yes , after you enter the data from form you can see this entry in table.

    then you had mentioned that you want column like student ID, student name.

    here I want to suggest you that if you are trying to save student ID then you not need to store student name.

    because from student id you can fetch all the details regarding that student like name, address, age, DOB etc.

    so why you want to store duplicate data.

    you had mentioned that,"i'd like for these to be a separate table holding student marks for each assignment (would this be done using a report?). "

    you can store the marks in Assignment table for each assignment.

    if you want to create an another table then also you can create it. its upon you.

    then you can store assignment_Id and marks in that table. so that you can fetch the mark for particular assignment.

    overall I want to say that , first try to properly design your database.

    Regards

    Deepak  


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 4, 2017 6:51 AM
    Moderator
  • Thanks for answering my questions Deepak.

    I will work on learning and developing a database design for now. I think the three tables I will include are the classes, students, and assignments table and will look into where to actually put student marks. The assignments table will have a list of all the assignments but wont have student marks in them. I was thinking of having a fourth table with the student IDs in the rows and the assignment ID, four criterias (K, T, C, A) and calculated total percentage in the columns. Would this work or should student marks be stored in another way.

    I want to create an entry form where the assignment is created. It would have assignment name and four check boxes for the criterias being assessed in that assignment (not all criterias are always assessed). Then it would have a textfield beside each box with the max # of marks for that criteria. After creating the assignment the teacher should be able to input student marks for the assignment (for example, Billy may have scored 10/20 in knowledge, 15/20 in thinking and 0/10 in application with communication not assessed in this assignment). With the marks for all the criterias inputted, there should be a calculation of the final mark (25/50 for Billy). 

    Tuesday, April 4, 2017 4:38 PM

  • I want to create an entry form where the assignment is created. It would have assignment name and four check boxes for the criterias being assessed in that assignment (not all criterias are always assessed). Then it would have a textfield beside each box with the max # of marks for that criteria.

    Having multiple Boolean (Yes/No) columns for each criterion is bad relational database design.  It is what is known as 'encoding data as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    In database terms assignments represent a many-to-many relationship type between students and  criteria, with marks as an attribute of the relationship type.  A many-to-many relationship type is modelled by a table which resolves it into two or more one-to-many relationship types.  So the model will, in broad outline, comprise the following tables:

    Students
    ….StudentID  (PK)
    ….FirstName
    ….LastName

    Criteria
    ….CriterionID  (PK)
    ….Criteria

    This table will currently have four rows, one for each of the four criteria which you have described

    Assignments
    ….StudentID  (FK)
    ….CriterionID  (FK)
    ….AssignmentDate
    ….Mark

    The primary key of this table is a composite one of the two foreign keys StudentID  and CriterionID  if a student can only be assigned each criterion once, but will also include AssignmentDate if a student can be assigned the same criterion on more than one occasion.  If the former is the case you might not need an AssignmentDate column; that's for you to judge.

    The interface for this model would be a students form, in single form view, within which would be embedded an assignments subform, in continuous forms view.  The subform would be linked to the parent form on StudentID.  For each relevant criterion a row would be inserted into the subform.  The StudentID value is inserted automatically into the table by the linking mechanism, so the subform's bound controls would be a combo box bound to CriterionID but set up in the usual way to hide its bound column and show the text Criterion value from Criteria, a text box bound to AssignmentDate if you have this column in the table, and a text box bound to the Mark column.

    You might like to take a look at StudentCourses.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates a basic many-to-many relationship type similar to yours.  The demo illustrates an number of possible interfaces, but the model for you to follow is that of a conventional form/subform.  The other interfaces are not suitable in your case as they would not allow the entry of the non-key attribute Mark in to the table which models the relationship type.  The Status attribute in my demo is analogous to your Mark attribute, though in your case it would not be looked up from a referenced table via a combo box as the Status is in mine, but simply entered into a text box.



    Ken Sheridan, Stafford, England

    Tuesday, April 4, 2017 10:32 PM