Results 1 to 7 of 7
  1. #1
    Tandem is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    15

    Attendance Form, Please Help!

    I'm working at a not-for-profit daycare which has many programs. This week, I have been working on our database. Forms for adding/editing clients and assigning them to programs are working great. The only thing left is for me to come up with a way to take attendance.

    The form I'm trying to create will be taking attendance of parents, not children (if any of their children are there, the parent is considered present).

    Here are the relevant tables that I have:

    tblParents
    ParentID
    ParentLast
    ParentFirst

    tblProgram
    ProgramName

    tblParentAndProgram (joint table so that a many to many relationship can be established between Parents and Program)
    ParentID
    ProgramName

    tblAttendance (this is where I become uncertain)ParentID
    Day
    Status
    Program



    tblStatus (not sure if this is even necessary)
    Status (present/late/absent)

    I'm trying to make a form in which you select a program from a combo box and it shows all of the parents who have registered for that program. Then every field would be populated except for Status. All the information would go to tblAttendance.

    What table relationships do I need to set up in order to take attendance? A one-to-many from ParentAndProgram to Attendance?
    Which fields do I include in my form, or do I base it on an append query?

    Any help would be appreciated. I can make this in excel if I need to, but it would be nice to keep everything together in Access.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    tblParents.parentID --> tblAttend.ParentID

    I think tblAttend needs to be keyed on 3 fields
    ParentID
    Day
    Program

    so, 1 parent ,in 1 class on 1 day cant be repeated.

    status may be used for ...present/absent/quit
    the tblProgram is only a lookup table and not in the relationship.

  3. #3
    Tandem is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    15
    Thanks for the quick response. I now have a relationship going from tblParents.parenID --> tblrAttend.ParentID, and 3 primary keys in tblAttend.

    As it is now, I can create a form which will enter one record at a time. I'll set the default value of Day to today(), which takes care of that, but I'd still like to be able to autopopulate every field in my form except for Status, and I need to show every parent registered for a given program when the program is selected from a combobox.

    This makes me think that I need to base a form on tblParentsAndProgram, so that when I use the combobox it will filter to show parents by program, but then I somehow need to relate tblParentsAndPrograms to tblAttend in order to get all the fields on the form.

    Forgive me for my ignorance, but how can I design this form?

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    and 3 primary keys in tblAttend.
    Not the best way. One PK field (usually Autonumber / use indexes to provide uniqueness).
    See http://www.fmsinc.com/free/newtips/primarykey.asp
    Read this page twice.

    Is this close? (feel free to modify and use as much as you want.)
    Attached Files Attached Files

  5. #5
    Tandem is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    15
    Something like that would probably do the trick, so I guess subforms are the way to go? Although I was hoping to do it without a subform.

    Ideally the form would look like:

    [Combo Box] <- select program

    [Parent ID/Name1] [Date] [Present/Absent]
    [Parent ID/Name2] [Date] [Present/Absent]
    [Parent ID/Name3] [Date] [Present/Absent]

    These fields would only show up after selecting the program from the combobox.

    Attached is a slightly older version of the database, before I started trying to implement attendance. I can't get the newest version until Monday.
    Attached Files Attached Files

  6. #6
    Tandem is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    15
    I solved this problem by creating a SessionID in tblParentAndProgram, removing some unnecessary fields, and creating a continuous form based on ParentAndProgram with a subform in the footer for attendance.

    The only real thing left is to filter the data based on combobox by selecting the program (or other method, if someone has a good idea). It would be nice to have no records show until the combobox is used, as well.

    Would a potential method be to update the record via the combobox using select/from, and set default value to " " with the same event on-load?

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If you used a WHERE Clause of " ", no records would be in the recordset.
    You need ALL records to be in the record set - at least all if the relevant records. For example, you have a "Active/Inactive" field. So relevant records would include "Active". Then you would use "....WHERE CustStatus = 'Active'" and still be able to use the following.



    What I have done in the past is to use the select/from. Then in the form on-load event, use something like

    Example:
    Form record source
    Code:
    SELECT Field1, Field2, Field3 FROM Table1
    Form load event
    Code:
    Private Sub Form_Load()
        Me.Filter = 1 = 2
        Me.FilterOn = True
    End Sub

    Combo box afterupdate event:
    Code:
    Private Sub cbopro_AfterUpdate()
        Me.Filter = "Macaddress = '" & Me.cbopro & "'"
        Me.FilterOn = True
    End Sub
    To show all records using a button
    Code:
    Private Sub ShowAll_Click()
        Me.Filter = ""
        Me.FilterOn = False
    End Sub
    To re-hide all records using a button
    Code:
    Private Sub HideAll_Click()
        Me.Filter = 1 = 2
        Me.FilterOn = True
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 03-23-2015, 03:09 PM
  2. Replies: 6
    Last Post: 01-16-2014, 12:41 PM
  3. Using Calender Form for Student Attendance
    By GSP101 in forum Database Design
    Replies: 6
    Last Post: 02-28-2013, 02:10 AM
  4. Database of attendance
    By antoniopom in forum Database Design
    Replies: 1
    Last Post: 01-19-2010, 06:49 AM
  5. Attendance Database
    By Bishop2ya in forum Queries
    Replies: 0
    Last Post: 03-10-2009, 02:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums