+ Reply to Thread
Results 1 to 11 of 11

Access for gradebook

  1. #1
    Registered User
    Join Date
    03-29-2020
    Location
    USA
    MS-Off Ver
    MS Office 2016
    Posts
    12

    Access for gradebook

    All -

    I would like to create my gradebook using access. There's a lot of websites and other forums that describe this, but are either extremely vague or turn out not to be gradebooks.

    Please comment on my proposed structure -

    Table 01 - student information: ALPHA is the primary key
    Table 02 - homework: ALPHA is the primary key. Each ALPHA is a new row and each homework set is a new column.

    Is this the ideal structure or is there a better way to do it?

    Thanks!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Access for gradebook

    I would not use Alpha for the Primary Key. I would use an auto number. In table two, you will need to add a Foreign Key to link to the Primary in Table one. Each homework should not be a new column. That is Spreadsheet mindset. I would prefer the following structure.

    StudentTable
    -------------------
    ID (PK) autonumber
    Lname Text
    Fname Text
    Other Contact Info Fields as needed

    HomeworkTable
    -------------------
    ID (PK) Autonumber
    fID (FK) Number
    Assignment Text
    DueDate Date
    Grade Number

    Link the two tables on the Student Table ID and the HomeworkTable fID

    Bind your tables to forms--the homeworkForm is a subform of the StudentForm

    Enter all data in your Forms.

    Use Queries to extract needed information.

    The attached should help to understand good DB design
    Attached Files Attached Files
    Last edited by alansidman; 03-29-2020 at 12:04 PM.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    03-29-2020
    Location
    USA
    MS-Off Ver
    MS Office 2016
    Posts
    12

    Re: Access for gradebook

    Thank you for the explanation and the word doc!

    So the ID (PK) in the StudentTable is an auto number generated by Access. Will that be a different ID (PK) in the HomeworkTable?

    Can the fID (FK) number in the HomeworkTable be the ALPHA in mentioned? For me the ALPHA is the student ID # which is unique and never repeats.

    "Link the two tables on the Student Table ID and the HomeworkTable fID" I'm sorry I don't really understand how you do that. Aren't those two different numbers?

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Access for gradebook

    They need to be the same number. You can add the Alpha as an identifier, but I prefer that your record identifiers be the auto numbers. It makes life easier. Check out this link on building an Access DB. ---> http://www.accessmvp.com/strive4peace/

  5. #5
    Registered User
    Join Date
    03-29-2020
    Location
    USA
    MS-Off Ver
    MS Office 2016
    Posts
    12

    Re: Access for gradebook

    I'm starting to get it. So would it be best to have each homework assignment in its own table?

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Access for gradebook

    No. One table for all homework. Look at my first post that shows how to set up that table. Remember the concepts of 1st Normal Form, 2nd Normal Form, etc. No repeats.

  7. #7
    Registered User
    Join Date
    03-29-2020
    Location
    USA
    MS-Off Ver
    MS Office 2016
    Posts
    12

    Re: Access for gradebook

    Okay, what is the best way to sum all of the row entries for the homework columns? I need a sum and a count. For what it's worth I've finally realized the access is nothing like excel and I'm registered for an access class.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Access for gradebook

    Depending on where you want the sums and counts.
    In a query you would use the Group By function. The large Sigma Icon.
    If in a form, then you would use the DSum and DCount functions.

    You can look at the syntax for each of these in www.techonthenet.com

    You are absolutely correct. Access is nothing like Excel. It is a totally different mindset as to how it is set up and used.

    I like to think of Access as a large Garbage Bin into which you throw Data. It is not sorted in any particular manner. When you want the data extracted , you build queries to match the data requirement to the information in the Garbage Bin and voila!
    Last edited by alansidman; 04-02-2020 at 02:24 PM.

  9. #9
    Registered User
    Join Date
    03-29-2020
    Location
    USA
    MS-Off Ver
    MS Office 2016
    Posts
    12

    Re: Access for gradebook

    Thanks. Maybe I need this spelled in crayon. Yes, I see the DSUM and DCOUNT functions, but I can't figure out how to use them across a row.
    I either need to figure out how to sum/count across a row or how to transpose my rows into columns. Maybe a union query?

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Access for gradebook

    Sums across fields are done in Queries. It sounds that if this is something in a table that you may have a normalization issue in your structure.

  11. #11
    Registered User
    Join Date
    03-29-2020
    Location
    USA
    MS-Off Ver
    MS Office 2016
    Posts
    12

    Re: Access for gradebook

    Got it! (sort of)

    For getting the sum I have a field in a query with the formula -
    Nz([CH12],0)+Nz([CH12],0)+...

    This works! I'm having a hard time getting DCount to work. I just need access to tell me if there's a value in the field or not. DCount might not be the most appropriate tool.

    Thanks, you're the best!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. School Gradebook
    By ntopea in forum Excel General
    Replies: 2
    Last Post: 06-20-2017, 11:58 AM
  2. Help With Gradebook and #DIV/0 error
    By adunning in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-02-2013, 12:47 AM
  3. Gradebook
    By JameE117 in forum Excel General
    Replies: 1
    Last Post: 11-30-2009, 06:31 PM
  4. Gradebook problems!
    By snyderra in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-23-2007, 08:30 AM
  5. Gradebook Help Please
    By LucasBuck in forum Excel General
    Replies: 4
    Last Post: 01-11-2006, 03:30 PM
  6. Gradebook woes
    By G. Doughty in forum Excel General
    Replies: 3
    Last Post: 03-08-2005, 08:06 PM
  7. Need help with gradebook
    By LucasBuck in forum Excel General
    Replies: 1
    Last Post: 02-27-2005, 02:16 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1