Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2011
    Posts
    9

    Question Unanswered: Attendance Reports and Student Rosters within Access

    First off, let me introduce myself- my name is Theodore Crawford. I generally do IT work for Houston Piano Company, but have been tasked with creating a database for our students taking lessons. There are approximately 100 students, and 35+ "classes", which are hour-long sessions on different days with different teachers. I've never really used Access, but have managed to create the basic framework of this database (tables, forms, and a few queries) and enter in most of the students.

    I'm a bit stumped with the "Reports", though. I need to create a report (or something within this database) that can be used to generate class rosters (student names and phone numbers) with space for the teachers to record the student's attendance (present, tardy, absent-unexcused, absent-excused). We'll be printing these Roster/Attendance sheets on a monthly basis and the teachers will be turning them in at the end of the month for me to enter into the system. Not the most efficient way to do things, but that's what they want to do.

    How do I go about creating this report? For that matter, how can I create a printable roster for each class? I'm using the following data variables (or whatever you want to call it)-

    • Scheduled Time (e.g., 1:00 - 2:00pm)
    • Scheduled Date (e.g., Wednesday)
    • Age (e.g., Adult)
    • Level (e.g., Beginner)
    • Teacher (i.e., teacher's name)
    • Room (e.g., Room 1a)
    • Student Name
    • Phone Number

    For example, I'd like to be able to generate a roster for the Wednesday 1:00-2:00PM class being taught by Jane Doe. Then, expanding upon that class roster, I'd like to be able to generate and print an attendance sheet that can be filled in by the teacher of that class (with a separate column for each week).

    This is all new to me, so I apologize if I'm asking stupid questions or not being concise enough. My proficiency lies in hardware, networking, and OS operations- not database management and data entry.

    Any and all help is greatly appreciated! Thanks in advance.

  2. #2
    Join Date
    Oct 2011
    Posts
    9
    ...anything, guys?

  3. #3
    Join Date
    Oct 2011
    Posts
    9
    Wow guys, nothing?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It would be easier if you could provide a description (Table name, column names, data types) for every table in your database that are involved in the process you describe.
    Have a nice day!

  5. #5
    Join Date
    Oct 2011
    Posts
    9
    Quote Originally Posted by Sinndho View Post
    It would be easier if you could provide a description (Table name, column names, data types) for every table in your database that are involved in the process you describe.
    Well alright then, here we go-


    Table Name = Students

    Column 1 = ID (Auto-Number)
    Column 2 = Last Name (Text)
    Column 3 = First Name (Text)
    Column 4 = E-Mail Address (Text)
    Column 5 = Notes (Memo)
    Column 6 = Attachments (Attachment)
    Column 7 = Scheduled Day (Text)
    Column 8 = Scheduled Time (Text)
    Column 9 = Teacher (Text)
    Column 10 = Phone Number (Text)
    Column 11 = Start Date(Text)
    Column 12 = Age (Text)
    Column 13 = Paperwork Available (Text)
    Column 14 = aimSI Customer Number (Text)
    Column 15 = Start Type (Text)
    Column 16 = Room (Attachment)
    Column 17 = Level (Text)


    Table Name = Guardians

    Column 1 = ID (Auto-Number)
    Column 2 = Company (Text)
    Column 3 = Last Name (Text)
    Column 4 = First Name (Text)
    Column 5 = E-Mail Address (Text)
    Column 6 = Business Phone (Text)
    Column 7 = Home Phone (Text)
    Column 8 = Mobile Phone (Text)
    Column 9 = Fax Number (Text)
    Column 10 = Address (Text)
    Column 11 = City (Text)
    Column 12 = State (Text)
    Column 13 = Zip (Text)
    Column 14 = Country (Text)
    Column 15 = aimSI Customer Number (Text)
    Column 16 = Notes (Memo)
    Column 17 = Attachment (Attachment)


    Table Name = Student Attendance

    Column 1 = ID (Auto-Number)
    Column 2 = Student (Number)
    Column 3 = Attendance Date (Date/Time)
    Column 4 = Status (Text)


    Table Name = Students and Guardians

    Column 1 = StudentID (Number)
    Column 2 = GuardianID (Number)
    Column 3 = Relationship (Text)
    Column 4 = Emergency Contact (Yes/No)

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I rebuilt the tables from the info you posted and I suppose that the relationships among these are as shown in the attachment.

    1. Table [Students]
    - If you intend to do anything useful with the column [Start Date], you should convert it from Text to Date/Time.
    - You should replace [Age] with [Date of Birth](Date/Time): the age changes and can be computed from the date of birth which is invariant.
    - [Scheduled Day], [Scheduled Time], [Teacher], [Paperwork Available], [Room ], [Level] and possibly [Start Date], [Paperwork Available],[Start Type] should be moved to another table (Classes): These data can change along time for a student (not sure for the last three).

    2. I would add a table for the teachers.

    Quote Originally Posted by CheoTrawford View Post
    ... how can I create a printable roster for each class?
    • Scheduled Time (e.g., 1:00 - 2:00pm)
    • Scheduled Date (e.g., Wednesday)
    • Age (e.g., Adult)
    • Level (e.g., Beginner)
    • Teacher (i.e., teacher's name)
    • Room (e.g., Room 1a)
    • Student Name
    • Phone Number
    3. Is it the [Phone Number] data from the [Students] table or from the [Guardians] table?

    Quote Originally Posted by CheoTrawford View Post
    ... For example, I'd like to be able to generate a roster for the Wednesday 1:00-2:00PM class being taught by Jane Doe. Then, expanding upon that class roster, I'd like to be able to generate and print an attendance sheet that can be filled in by the teacher of that class (with a separate column for each week).
    4. Where can the info about a class be found?

    5. Just to be sure: What is the table [Student Attendance] used for? Is it where (present, tardy, absent-unexcused, absent-excused) is stored?
    Attached Thumbnails Attached Thumbnails Roster_Relationships.jpg  
    Have a nice day!

  7. #7
    Join Date
    Oct 2011
    Posts
    9
    Quote Originally Posted by Sinndho View Post
    I rebuilt the tables from the info you posted and I suppose that the relationships among these are as shown in the attachment.

    1. Table [Students]
    - If you intend to do anything useful with the column [Start Date], you should convert it from Text to Date/Time.
    - You should replace [Age] with [Date of Birth](Date/Time): the age changes and can be computed from the date of birth which is invariant.
    - [Scheduled Day], [Scheduled Time], [Teacher], [Paperwork Available], [Room ], [Level] and possibly [Start Date], [Paperwork Available],[Start Type] should be moved to another table (Classes): These data can change along time for a student (not sure for the last three).

    2. I would add a table for the teachers.


    3. Is it the [Phone Number] data from the [Students] table or from the [Guardians] table?


    4. Where can the info about a class be found?

    5. Just to be sure: What is the table [Student Attendance] used for? Is it where (present, tardy, absent-unexcused, absent-excused) is stored?

    1a. We don't really plan on using the Start Date for anything as far as I know. It's really not even something we have on record for every student.

    1b. I can do that. I'm pretty sure birth dates are on just about every registration form.

    1c. I'll move the first six over to a new table names "Classes" per your recommendation.

    2. What would I be putting in this table? Obviously their names, but what else? We only have three teachers at the moment.

    3. It's from the [Students] table.

    4. I'm not sure I know what you mean. The way I have it set up now, the following fields are basically what make up a "class"-
    • Scheduled Day
    • Scheduled Time
    • Teacher
    • Level
    • Age
    • Room

    Those are all currently stored within the [Students] table, but I'll be moving them over to a new table named "Classes".

    5. That's correct.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In addition to the table Class, I also created three more table. Here the structure of the four extra tables:
    Code:
    Table name:      Classes            
    ========================                  
    Column name      Data type      Index                  Constraint
    ------------------------------------------------------------------
    ID               AutoNumber     Primary key      
    Scheduled Day    Long Integer   Yes (Duplicates OK)    Between 1 And 7
    Scheduled Time   Date/Time      Yes (Duplicates OK)      
    Teacher          Text            
    Level            Text            
    Room             Text            
    
    Table name:      Students and Classes  (junction table)
    =======================================================           
    Column name      Data type      Index
    -------------------------------------      
    Student_ID      Number \_______ Primary           
    Class_ID        Number /        Key   
    
    Table name:     Calendar  (tally table)
    =======================================           
    Column name      Data type      Index  
    ---------------------------------------    
    Cal_Date         Date/Time      Primary key      
    Day_Of_Week      Number         Yes (Duplicates OK)      
                      
    Table name:     WeekDays  (lookup table)
    ========================================          
    Column name      Data type      Index            Constraint
    -----------------------------------------------------------
    Day_Code         Number         Primary key      Between 1 And 7
    Day_Name         Text                            In ('Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday')
    [Students and Classes] is a junction table that creates the relationships between Students and Classes.

    [Calendar] is a tally table that enumerates the days for one or more years. The [Day_Of_Week] column is not strictly necessary as it can be computed from [Cal_Date]. I added it to improve the performance when it's used in queries. Here's a function to fill it:
    Code:
    Function CreateCalendar(ByVal StartYear As Long, Optional ByVal YearCount As Long = 1)
    
        Dim strSQL As String
        Dim dteDate As Date
        
        dteDate = CVDate("1/1/" & StartYear)
        Do
            strSQL = "INSERT INTO Calendar ( Cal_Date, Day_Of_Week ) " & _
                                   "VALUES ( #" & Month(dteDate) & "/" & Day(dteDate) & "/" & Year(dteDate) & "#, " & _
                                    DatePart("w", dteDate, vbSunday) & " );"
            CurrentDb.Execute strSQL, dbFailOnError
            dteDate = DateAdd("d", 1, dteDate)
        Loop Until Year(dteDate) = StartYear + YearCount
            
        
    End Function
    [WeekDays] is a lookup table that associates the names of the day of the week with their numeric counterparts. Its contents is as follows:
    Code:
    Day_Code	Day_Name
    ---------------------------
         1  	Sunday
         2  	Monday
         3  	Tuesday
         4  	Wednesday
         5  	Thursday
         6  	Friday
         7  	Saturday
    From there, we can retrieve the Students/Classes data using this query:
    Code:
    SELECT Students.[First Name], 
           Students.[Last Name], 
           WeekDays.Day_Name, 
           Classes.[Scheduled Time], 
           Classes.Teacher, 
           Classes.Level, 
           Classes.Room
        FROM (([Students and Classes] 
            INNER JOIN Students ON [Students and Classes].Student_ID = Students.ID)
                INNER JOIN Classes ON [Students and Classes].Class_ID = Classes.ID) 
            INNER JOIN WeekDays ON Classes.[Scheduled Day] = WeekDays.Day_Code;
    We can create a calendar for each class using:
    Code:
    SELECT WeekDays.Day_Name, 
           Classes.[Scheduled Time], 
           Classes.Teacher, 
           Classes.Level, 
           Classes.Room, 
           Calendar.Cal_Date
        FROM WeekDays 
            INNER JOIN (Classes 
                INNER JOIN Calendar ON Classes.[Scheduled Day] = Calendar.Day_Of_Week) 
                ON WeekDays.Day_Code = Classes.[Scheduled Day]
        ORDER BY Classes.ID, Calendar.Cal_Date;
    The query to create the Attendance reports should probably be something like (plus a WHERE clause to isolate each class):
    Code:
    SELECT Calendar.Cal_Date, 
           WeekDays.Day_Name, 
           Classes.[Scheduled Time], 
           Classes.Teacher, 
           Classes.Level, 
           Classes.Room, 
           Students.[First Name], 
           Students.[Last Name], 
           [Student Attendance].Status
        FROM ((Students INNER JOIN ((Classes 
            INNER JOIN Calendar ON Classes.[Scheduled Day] = Calendar.Day_Of_Week) 
            INNER JOIN [Students and Classes] ON Classes.ID = [Students and Classes].Class_ID) ON Students.ID = [Students and Classes].Student_ID) 
            LEFT JOIN [Student Attendance] ON Students.ID = [Student Attendance].Student) 
            INNER JOIN WeekDays ON Classes.[Scheduled Day] = WeekDays.Day_Code
        ORDER BY Calendar.Cal_Date;
    The diagrams of the three queries and the general diagram of the database are in the attachments.

    Personally I would add a relationship for the table Attendance but I'm not sure of how you want to handle it.

    Using the Report Assistant, it should not be difficult to create the sheets from there.
    Attached Thumbnails Attached Thumbnails Roster_Relationships.jpg   Qry_Students_Classes.jpg   Qry_Class_Calendar.jpg   Qry_Attendance.jpg  
    Have a nice day!

  9. #9
    Join Date
    Oct 2011
    Posts
    9
    Oh my.. thank you so much!! I'll get that all into the database tonight and let you now how it goes! Thank you, thank you, thank you!!!

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  11. #11
    Join Date
    Oct 2011
    Posts
    9
    Quote Originally Posted by Sinndho View Post
    You're welcome!
    Ok, so I entered everything in as you specified, and have checked and made sure all of the relationships are correct. I have one question, though- what am I supposed to do with the function that you gave me that you said would be used to fill the [Calendar] table? I wasn't sure what to do with it, so I created a module and stuck the function in there. I'm 99% positive that's not what I was supposed to do with it, though.

    Also, none of the queries are populating. I'm not sure if I did something wrong, or if they're just not supposed to and I'm not understanding the purpose of queries. I'm almost positive I created all of the tables correctly, and I created the queries from the SQL code you gave me, so that should theoretically all be fine.

    As I'm sure you've noticed, I have a fundamental lack of knowledge when it comes to SQL, VBA, and Access in general.. you've been a great help so far in spite of my lack of Access enlightenment, though! Thanks again.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by CheoTrawford View Post
    I wasn't sure what to do with it, so I created a module and stuck the function in there. I'm 99% positive that's not what I was supposed to do with it, though.
    That was the right thing to do: create a function into a module. I guess that the queries do not return anything because the Calendar table does not exist or is empty. When it's created, you need to call the function CreateCalendar to fill it (it's quicker than filling it manually!). To do so, open the module where it is located, then open the Immediate window (Ctrl+G) and type something like this into it:
    Code:
    CreateCalendar 2011, 5
    This example will fill the Calendar table with dates beginning on 01/01/2011 and up to 12/31/2015. This procedure should only be used once, unless you want to add more years to the Calendar table. Don't try to use it twice for a year that already exists in the Calendar table, as this is forbidden by the primary key defined on this table.
    Have a nice day!

  13. #13
    Join Date
    Oct 2011
    Posts
    9
    Quote Originally Posted by Sinndho View Post
    That was the right thing to do: create a function into a module. I guess that the queries do not return anything because the Calendar table does not exist or is empty. When it's created, you need to call the function CreateCalendar to fill it (it's quicker than filling it manually!). To do so, open the module where it is located, then open the Immediate window (Ctrl+G) and type something like this into it:
    Code:
    CreateCalendar 2011, 5
    This example will fill the Calendar table with dates beginning on 01/01/2011 and up to 12/31/2015. This procedure should only be used once, unless you want to add more years to the Calendar table. Don't try to use it twice for a year that already exists in the Calendar table, as this is forbidden by the primary key defined on this table.
    Alright, I did that and it populated the Calendar table as well as the Class Calendar query. That's one problem solved!

    I manually filled in the Classes table, which looks something like this-
    Code:
    ID   Sch. Day    Sch. Time     Teacher     Level      Age       Room
    -----------------------------------------------------------------------------
    1        3        12:00 PM    Pat Guard   Beginner   Adult     Room 3a
    2        3         1:00 PM    Pat Guard   Beginner   Adult     Room 3a
    
    And so on and so forth...
    I think that's what I was supposed to use that table for.. Anyway, the Students and Classes table is blank. I'm not sure if that's supposed to fill itself based on the relationships it has with the other table's primary keys, or if I'm supposed to go through and fill it the same way I filled the Classes table. If that's the case, that table will take quite a while to fill manually.

    The WeekDays table is serving it's purpose, as evidenced by the contents of the Class Calendar query.


    Now onto the queries-

    The Attendance Report query is blank, but that may just be because I haven't entered any attendance data for any of the students yet.

    The Students/Classes query is also blank. I have no idea what's going on with that one.. the query was created from the SQL code you gave me, and the relationships all look correct.. I'm clueless

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The Students and Classes table must be filled in as it defines which student attends which class. It is involded in JOINS of several queries which won't yield any result is this table remains empty.
    Have a nice day!

  15. #15
    Join Date
    Oct 2011
    Posts
    9
    Quote Originally Posted by Sinndho View Post
    The Students and Classes table must be filled in as it defines which student attends which class. It is involded in JOINS of several queries which won't yield any result is this table remains empty.
    Alright, so that needs to be manually filled. Will I have to continue to have to manually update that table as I add new students, or will it auto-fill as I enter in new data?

Posting Permissions

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