Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2012
    Posts
    6

    Unanswered: how do I get access to do GPA's per record per row and student attendance ?

    I am designing a database for a building trades class, and learning Microsoft access as I go

    (I’ve read Microsoft Access 2007 Comprehensive by Adamski & Finnegan three times) so we are clear, I may misuse terminology please pardon my mistakes:

    This course is a 350 hour course with 50 lab tasks, 57 chapters & 57 quizzes, 4 Mid-terms, and lastly 4 final exams (a final is given at the end of each session there are 4 sessions total)- ALL of which I hope to use to store, query and report the results of each fields value for a class of 15 to 34 students, that graduate within 3 to 4 months from the date of enrollment which is not static what I mean here is that new students are constantly enrolling, and starting at different parts of the book and Course content (i.e. where Rosie the Riveter started on Dec 27 2012 while session 2 was going on she’ll go through safety first but she’ll still be expected to do session two work, and Marylyn Monroe started on Jan. 1 2013, in session 2 also, just at a different point in the course work)

    Here is what I have envisioned and created from that vision thus far:

    I created a really WIDE table called “tblstudents” with a total of 218+ fields (no more than 256)

    The foresaid table is the backend, I then created a form in design view from scratch called: “frmtblstudentstbd” (Hungarian notation for: form table students tabbed since it’s a tabbed form)

    This tabbed form will be the de facto FRONTEND for the professor and clerks to use for entering data into the table, running queries and generating reports employed buttons on the tabbed form to run queries & report and navigate. It’s a dashboard that’s accessed from the switchboard.

    I am having several problems that are preventing me from completing this project I’ve volunteered to to in hopes of learning access.
    I can’t figure out how to keep track of student hours, and daily,weekly,monthly, and quarterly attendance

    And

    I can’t figure out how to calculate the students grade point averages.
    It’s important that each row in tblstudents is unique and that their grades are averaged based on field values in that students row-much in the same way one would use an excel spread sheet with a cell formula =avg (field1:field57)

    I can get access to average the students grades but its literally ALL of the students grades being averaged in the TOTALS row at the bottom of the table, at the bottom of a given field.
    This highly undesirable-I need to average grade points PER ROW PER RECORD not all records in the table. Make sense ? if it’ll help ,I can show screen shots and I don’t mind sharing the database at all.

  2. #2
    Join Date
    Aug 2012
    Posts
    6
    Here's a clean version of the datbase the names aren't the same as in my post. students is "tblstudents" etc.
    Attached Files Attached Files

  3. #3
    Join Date
    Aug 2012
    Posts
    6
    revised ed.

  4. #4
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by dbconstructor View Post
    I created a really WIDE table called “tblstudents” with a total of 218+ fields (no more than 256)
    This is likely the source of your problems. If the data is stored correctly, you would be able to produce the desired queries. Right now (as you mentioned) you have a glorified Excel spreadsheet which is of little value to you.

    You need to make several TALL tables instead of one WIDE. Do some reading on database design... there's a reason why the data is stored that way.. so you can get accurate info back out!!

    Quote Originally Posted by dbconstructor View Post
    I am having several problems that are preventing me from completing this project I’ve volunteered to to in hopes of learning access.
    I can’t figure out how to keep track of student hours, and daily,weekly,monthly, and quarterly attendance

    And

    I can’t figure out how to calculate the students grade point averages.
    It’s important that each row in tblstudents is unique and that their grades are averaged based on field values in that students row-much in the same way one would use an excel spread sheet with a cell formula =avg (field1:field57)

    I can get access to average the students grades but its literally ALL of the students grades being averaged in the TOTALS row at the bottom of the table, at the bottom of a given field.
    This highly undesirable-I need to average grade points PER ROW PER RECORD not all records in the table. Make sense ? if it’ll help ,I can show screen shots and I don’t mind sharing the database at all.
    So, imagine a world where your database was properly normalized...

    Calculating the GPA will probably need some VBA code but at least you can get to the answer.

    Steve

  5. #5
    Join Date
    Aug 2012
    Posts
    6
    Quote Originally Posted by sps View Post
    This is likely the source of your problems. If the data is stored correctly, you would be able to produce the desired queries. Right now (as you mentioned) you have a glorified Excel spreadsheet which is of little value to you.
    I need it to work like an excel spreadsheet does,what I need it to do like excel does is calculate per row "WIDE" rather than "TALL", however as you mentioned below and as I 've noted in development the database crashes when I run a macro/query against it because there wasn't enough memory in the machine I was developing on.

    I can drag an drop the fields I need into the tabbed form,which works for the front end, and I am not opposed to splitting the tables up if it'll accomplish my goals. a tblgrades and tblattndance seems logical to me with ID being the prmary key which relates the records to one another, does that make sense sir ? like a many to many relationship between the "ID" PK between each table ?

    Quote Originally Posted by sps View Post
    You need to make several TALL tables instead of one WIDE. Do some reading on database design... there's a reason why the data is stored that way.. so you can get accurate info back out!!
    Calculating the GPA will probably need some VBA code but at least you can get to the answer.
    That's exactly what I am hoping to get my head around steve, The code to parse the table or related tables and return the values per student. If I understood you correctly, with a very wide table VBA code, and SQL queries will not properly execute against such a wide table, where they would against several JOINED ones -
    is that right ?

  6. #6
    Join Date
    Aug 2012
    Posts
    6
    tblgrades for session1 then might look like this:
    ID (as primary key) I'd relate the ID field to the ID field in tblstudents (right?)
    gs1c1 gs1q1(each field is Grade Session 1 Chapter1, Grade Session 1 Quiz 1 et seq)
    gs1c2 gs1q2
    gs1c3 gs1q3
    gs1c4 gs1q4
    gs1c5 gs1q5
    gs1c7 gs1q7
    gs1c8 gs1q8
    gs1c9 gs1q9
    gs1c10 gs1q10
    gs1c11 gs1q11
    gs1c12 gs1q12
    gs1c28 gs1q28
    gs1c29 gs1q29
    gs1c30 gs1q30
    gs1c31 gs1q31
    gs1c32 gs1q32

    This would be normalized then and vba code would work to return the GPA for EACH record (each student) -is that correct ?

  7. #7
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by dbconstructor View Post
    tblgrades for session1 then might look like this:
    ID (as primary key) I'd relate the ID field to the ID field in tblstudents (right?)
    gs1c1 gs1q1(each field is Grade Session 1 Chapter1, Grade Session 1 Quiz 1 et seq)
    gs1c2 gs1q2
    gs1c3 gs1q3
    gs1c4 gs1q4
    gs1c5 gs1q5
    gs1c7 gs1q7
    gs1c8 gs1q8
    gs1c9 gs1q9
    gs1c10 gs1q10
    gs1c11 gs1q11
    gs1c12 gs1q12
    gs1c28 gs1q28
    gs1c29 gs1q29
    gs1c30 gs1q30
    gs1c31 gs1q31
    gs1c32 gs1q32

    This would be normalized then and vba code would work to return the GPA for EACH record (each student) -is that correct ?
    Absolutely Not! (For several reasons). Start by letting go of your Excel spreadsheet thinking and think about the result you are looking for: A GPA for each student.

    Secondly, from a database view the ID field in tblGrades is different than the ID field in tblStudents. The db will let you connect them since they are the same datatype but they don't relate. You can name your ID fields to match the table to keep things clearer.

    Anytime you are tempted to add fields like "grade1, grade 2, .... , grade x" STOP!! At this level of your understanding you NEVER want to do that. (There are some rare instances where it could be considered OK, but this isn't one of them). Your database tables are going to read like a database, not a spreadsheet. You need to accept this or you might as well go back to the spreadsheet.

    In a very basic view of this, you could create a few base tables. You'll need to do more than this but it should give you the idea:

    (in these examples, the first field is the Primary Key)
    tblStudent (studentId,name,email, etc)
    tblExams (examId, examName, examPoints)
    tblStudentExamGrades (gradeId, studentId, examId, score)

    Some notes:
    tblStudent - Unique pieces of info that occur once. No grades, attendance records, etc.

    tblExams- Contains quizzes, midterms, finals, etc. General information about the exam but no student specifics -- examPoints is the amount of points possible in this exam. You'll need this to compute the GPA on per exam basis. Without this, you can only calculate the GPA at the end of the course

    tblStudentExamGrades - Tracks the grades per student, per exam. Foreign Key references to both tblStudent and tblExam. This won't be easily read by a human but the database will like it.

    ---
    Now you can create some reasonable straight forward queries to calculate totals by student. As I've said previously, do some reading on database design to understand why this works better than an spreadsheet. Or if you prefer the more hands on approach, enter some data in both formats and try to answer simple questions like total points by student.

    Steve

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can always get back to the spreadsheet style of presentation if you need to, heck you can even pump data back into Excel or other spreadsheets.
    As Steve (SPS) suggests redesign your db
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2012
    Posts
    6

    I am trying to move a record from tblstudent, and I think I am going about it in the

    I am trying to move a record from tblstudent, and I think I am going about it in the wrong way.
    I created an SQL query called “qryGraduationStep1”:
    INSERT INTO tblStudentComplete (StudentID, fname, lname, enrolldate, completedate)
    SELECT StudentID, [lname], [fname], [enrolldate], [completedate]
    FROM tblStudent
    WHERE [completedate] is not null;
    ****this works to COPY the graduated/dropped student from tblstudents*****
    However, it does not REMOVE the record from tblStudent which I need to do since the table is for active students ONLY
    Because of the records relationship between tblStudentExamGrades it cannot be deleted from tblstudent using the SQL below:
    DELETE FROM tblstudent
    WHERE completedate is not null
    So I thought that if I were to delete the Students records from tblStudentExamGrades using:
    DELETE *
    FROM tblstudentExamGrades
    WHERE Graduated=Yes;
    Then I would be able to remove the student record from TblStudent, I was wrong that does not work.
    I want their grades to go with them when they are removed from TblStudent (regardless of whether or not they graduated or dropped the class)
    If the above statement worked TO effectively MOVE them out of TblStudent I’ll be doing so at the expense of the record of their grades, which of course defeats the purpose.
    For your consideration I am trying to keep the tables normalized, and I have attached a relationship report for your review.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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