Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    Ashland, Oregon, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    School Attendance app

    I need some ideas on this one.

    I have a table listing class names, and another related table listing the meeting dates of each class. This related table contains EACH date that each class meets.

    Then there is the student table. I need a way to track and record attendance of each student in each class for each meeting day of each class.

    (I'll also have to have data entry forms, and class rosters to print, with names down the left side and dates across the top, with checkboxes at the intersection.)

    I'm having a little difficulty seeing how do to set all this up. Whenever I get close with a query, it turns out to be non-updatable. :-(

    Thanks.

    --
    Bill

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: School Attendance app

    Hi Bill.

    You say: I need a way to track and record attendance of each student in each class for each meeting day of each class.

    You don't mention anything about an Attendance table that would tie Student, Class and date together. If you had all the positive attendance records, would that enable you to create the attendance roster?

    How do you intend to create attendance records?

    Does it work on paper? You may also wish to re-think the design of your rosters a bit. Sounds a bit like a job for a pivot table.

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Location
    Ashland, Oregon, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: School Attendance app

    Well, I do have an attendance table, consisting of student_prikey, class_prikey, and DateAttended. So, yes, I I could enter the data into the tbl_attendance from a paper copy of attendance records.

    However, I want to be able to PRINT out (and display via form for data entry) the attendance sheet prior to the class (to give to the instructor) and at any time during the class (thus showing attendance up to the printout date), with names on the left and dates on the top and checkboxes (checked if attended, empty if not) at the intersection. That does kinda sound like a job for a pivot table, but when using the wizard, if the source table (the students) changes (i.e., students drop or get added during the length of the course), then you have to rerun the wizard, and that is not acceptable from a user standpoint. Can VBA be used to generate the pivot table each time the form/report is displayed/run? How?

    As I mentioned, I can't seem able to create a query that will allow all this to happen, though I think it ought to be able to be done (i.e, the queries I get so far are all non-updatable).

    Although this is probably a very bad approach, my thinking runs like this: Get the data for all students in a specific class from the attendance table (many records may be blank or partly filled in, depending on where the class is, date-wise and who attended and who skipped out), turn that data into a matrix to display on the screen, accept user input (via those checkboxes at the intersection), then update the tbl_attendance with the array info. I'm sure that's not a very good approach, but that's the only way I can explain it now. :-)

    --Bill

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: School Attendance app

    I have already built just this system. For the Register part I used these tables: tblStudents; tblGroups; tblLessons and tblAttendances.
    tblGroups is 1:M with tblStudents.
    tblGroups is 1:M with tblLessons.
    tblStudents is 1:M with tblAttendances
    tblLessons is 1:M with tblAttendances.

    Your data entry form for Registers will need to be a main form/subform with Lessons on the main form and a list of Students attending on the subform. I pre-generated these with apppend queries run through code. You cannot have an updateable crosstab query so your form design with several lessons showing at once does not seem possible.
    Personally I used Excel to print out registers (exported a crosstab query and then ran Excel code to set up the sheet) as it does the sums and layout much better. You could do it in Access but changing the column headings in a crosstab query based report is v. slow and tortuous.

    HTH
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  5. #5
    New Lounger
    Join Date
    Jun 2003
    Location
    Ashland, Oregon, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: School Attendance app

    I'm afraid I don't understand your "shortcut." :-)
    What does "1:M" mean, and what does

    "tblGroups is 1:M with tblStudents."

    really mean?

    Instead of several lessons showing at once, I wanted to show one lesson with all students and their attendance for that one lesson (class) in a screen form, so that the users need only check/uncheck boxes to show day attended.

    Excel sounds like a neat idea, how do you do that?

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: School Attendance app

    Yes, I am in agreement with SteveH.
    Because you want the dates along the top horizontally (and the records are arranged vertically in the table), I was thinking of a pivot table. Usually, a pivot table can be refreshed (in Excel at least ), and it can be done via VBA.

    Because the number of students in the list can change as
    well as the number of dates (depending on the class), the form you wish to display (with the editable checkboxes at the student/date intersections) needs to be a form where the number of controls on the form can be dynamically increased/decreased. Not sure how to do that in Access, ie I have never tried that.

    This is not meant as a cop out, but my first choice would be to do this in Excel, getting the data from Access. I did a little test and, with a little more work, it could work the way I imagine you want it to look.
    Attached Images Attached Images

  7. #7
    New Lounger
    Join Date
    Jun 2003
    Location
    Ashland, Oregon, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: School Attendance app

    Hi, Ken.

    How do you get data into (and then back out of) Excel?

    The table you've show is (almost) exactly what I'm looking for, except I want the "1's" in the spreadsheet to be checkboxes so the data-entry folks can just check the box for the days each student has attended, and print out current rosters that show both the past attendance, as well as black boxes ('cause this is on paper) for the instructor to continue to mark up as the class progresses.

    And of course, if using Excel, that would imply the users have Excel installed, huh? :-)

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: School Attendance app

    I think getting the data into Excel, I'd use a Get External Data query, to load the Attendance recs from Access.
    To get the modified recs back into Access, I'd probably create an export file from Excel, load it into a temp table into Access (with File|Get External Data via VBA) and process each record to update the tblAttendance table.

    The 1's (ones) in the Pivot table is no problem, because you can't edit the pivot table anyway. You would need to copy the whole pivot table (with Copy Special/Values Only) to another sheet to make it editable (and delete the original pivot). Using a For Each loop you could change the ones to X's (I have never tried to insert an checkbox object into each cell this way). Is it important to enter a checkmark instead of an X?

    I think the biggest hurdle would be to update the original records from the modified roster, although it could be done again using a For Each loop and the Activecell.Offsett row,col to pull the data together. You would need some primary key to lookup the record in the Excel Attendance data range to update each record before exporting it back to Access.

    Whether the users would need Excel...Rarely is a carpenter able to do all jobs with a single tool. It usually takes a combination of tools to get a job done, which means proficiency with all these tools.

    Below is a subset of the data I used to create the pivot.
    Attached Images Attached Images

  9. #9
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: School Attendance app

    1:M means one to many hence one Group has many Students
    tblGroups would consist of fields like GroupID (Primary Key) and GroupName.
    tblStudents would consist of StudentID (Primary Key), GroupID (Foreign Key) and any other stuff you want on the Students.
    When (if) I have a lunch break I'll attempt to describe the Excel process and I'll post a couple of screendumps of my Register Form in Access and my Register Printout in Excel which may clarify things.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  10. #10
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: School Attendance app

    In outline, to print a Register, I run a crosstab query which takes a StaffID (the Teacher); a GroupID (the Group) and Start and End Dates as parameters. This query is then output to an Excel file. I then open that file (having started Excel first); copy the data into an Excel template and run an Excel procedure to format it, add formulas at the bottom of the sheet and on the right and to print it out. Finally close Excel.

    (pubFilePath is the path to where these Excel files are stored)
    Public Function PrintRegister(strCrsTitle As String, strGrp As String, strTut As String)
    On Error GoTo Err_PrintReg
    'Starts Excel and checks version (needs Excel97 for conditional formatting)
    Set appXl = New Excel.Application
    Set XlRegBk = appXl.Workbooks
    Set XlTempWkBk = appXl.Workbooks
    If appXl Is Nothing Then
    MsgBox "MS Excel is not installed on this computer!"
    GoTo Exit_PrintReg:
    ElseIf CInt(Left(appXl.Version, 1)) < 8 Then
    MsgBox "The required version of MS Excel (Excel 97) is not installed on this computer!"
    GoTo Exit_PrintReg:
    End If

    XlRegBk.Open pubFilePath & "Register.xlt"
    'Original Query called Registers
    DoCmd.OutputTo acQuery, "Registers", acFormatXLS, pubFilePath & "RegTest.xls"
    'Opens RegTest.xls
    XlTempWkBk.Open pubFilePath & "RegTest.xls"
    With XlTempWkBk("RegTest.xls").Sheets("Registers")
    .Range("A1").CurrentRegion.Copy
    End With
    With XlRegBk("Register1").Sheets("Register")
    .Range("A2").PasteSpecial (xlValues)
    'Puts in Heading Details
    .Range("B1") = strGrp 'Puts in Group Name
    .Range("C1") = strTut 'Puts in Tutor's Name
    .Range("H1") = strCrsTitle 'Puts in the Course Title
    End With
    appXl.CutCopyMode = False
    XlTempWkBk("RegTest.xls").Close (False)
    Set XlTempWkBk = Nothing
    appXl.Run ("Register1!Sheet2.PrintSheet")
    'Closes Excel template without saving
    appXl.DisplayAlerts = False
    XlRegBk("Register1").Close (False)
    appXl.DisplayAlerts = True

    Exit_PrintReg:
    'Any cleanup code required
    On Error Resume Next
    appXl.Quit
    Set XlRegBk = Nothing
    Set appXl = Nothing
    Exit Function

    Err_PrintReg:
    'Any Error-handling code required
    Resume Exit_PrintReg

    End Function
    Attached Files Attached Files
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  11. #11
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Winnipeg, Canada
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: School Attendance app

    Well Bill, it looks like you found your man.[img]/S/grin.gif[/img]

    Looking at the attachments, it definitely seems possible to do what you want to achieve. I think Steve has done a remarkable job.
    It also shows that by re-thinking the design and making some compromises, it is quite possible to achieve the required end result.

Posting Permissions

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