Attendance Form Help

[chellebell1689]

Ok, so I know I've asked about this alot, but I believe I've got my tables normalized correctly and I see the issue.

I've got a form I'm trying to make, Sunday School Attendance, and I've got three tables linked, Members, SSAttend, SSClass. Linked as follows: Members to SSAttend (One to Many / EnvNum), SSAttend to SSClass (Many to One / ClassID), and Members to SSClass (Many to One / ClassID).

Just a little about the tables, I just want to pull the first and last name, and OnRoll (yes/no field) from the Members table. ClassID, AttendanceMonth, S1_Attend - S5_Attend, and S1_Date - S5_Date from the SSAttendance table. The only information on the SSClass is the ClassID, SSClass, Teacher, Abreviation (what most people know the class as); not sure if I really need this table for this form or even at all.

Currently if I make the form, nothing is generated. I know it's not generating because there's no data in the SSAttend table. But how do I make it generate the names (and eventually sort by class and if they're on the roll or not) so that I can start adding information to the SSAttend table?

I hope I provided enough information. I have been able to get this to work, but that was when I put the names in the table for the first time then track everything after that, but that just seems like a ridiculous way to do it. Let me know if you need more information. Thanks in advance.

 

[plog]

Members to SSAttend (One to Many / EnvNum), SSAttend to SSClass (Many to One / ClassID), and Members to SSClass (Many to One / ClassID)

That's not a good relationship. Your tables shouldn't form a loop in their connections, there should be only one way to get from one table to another. Also, I believe you are missing a table--something like Enrollment. It would be a junction table (Many to Many) between SSClass and Members. That table would hold an ID for every person enrolled in every class. You would then use that ID in your SSAttend class to mark them as absent/attending.

Also, I believe you are basing your forms on queries. That too is improper. Forms that directly interact with data (add/edit/delete) should be based on tables.

Can you post your structure? And provide a plain-language overview of what you're database is to do? No database jargon. Talk to me like a 6th grader who knows nothing of your business.

 

[chellebell1689]

Ok, here's the database with just the tables and forms for this. Basically what I want it a form to show the names of people who are in a specific Sunday School class. Then for each Sunday of the month, I will select if they were present or not. I would like for the Date of each Sunday to the the column title and then each member had a check box under each date, but if I have to have the date in line with each check box, that's fine too. I would also like the form to be set up so that I can select the month and it will only show the Sundays & attendance for that month.

We take attendance each week and I add it to the computer the following Mon. I don't want a new record to be created each week, just once at the beginning of the month and updated weekly.

Did I explain that clear enough? (My mind is kinda jumping around today) Let me know if you need more info or anything else. Thanks.

 

[plog]

That section of database is neither related properly or normalized. When you start numerating field names (S1_, S2_, S3_...) its a sign that your structure is incorrect. Also, data input has no influence on table structure. Put your forms out of your mind for now and let's get the tables right.

I think my initial assesment was correct, you need an enrollment table. The class a member is in shouldn't be in the Member database, but in a new table, Enrollment. It would look like this:

Enrollment
Enroll_ID, autonumber, primary key
ClassID, number, foreign key to SSClass
EnvNum, number, foreign key to Members

I think a new table is necessary because there's actually a many to many relationship between members and classes. Over 5 years, couldn't members be in multiple classes?

Also, you need to decide if you want to track attendances, abscences or both. There's a difference and it effects your table structure. What kind of reporting are you going to do out of this? That will drive the structure.

 

[chellebell1689]

Real quick, the S1_, S2_, etc. Those are for each Sunday of the month. (1st Sunday, 2nd Sunday, so on.) I did this because I thought it would save space and be easier to generate how often someone attend if each month is on a row.

 

[plog]

I understand what they are for, not the right way to do things.

 

[chellebell1689]

K, now that I finished reading the rest of your post...

I understand the need for the Enrollment table, thank you for explaining that to me and I will add it. As for the attendance and absences, I kinda thought they went hand in hand. If June 14 shows a check, then they were present, but if it's blank, then they were absent.

Eventually the member's detail form will show the attendance history (you can see that in the Sunday School tab, which is one reason I left that form in the db). The pastor has expressed he would like to be able to view the following
- How many in Sunday School (all classes) on any given Sunday
- How many in Sunday School (just one class) on any given Sunday
- '' '' any given month
- '' '' for the year
- Attendance history for any member (either by class, month, year, or total)

I hope this helps show what is expected for the long run. Thanks for the help.

 

[plog]

They do go hand in hand, but data entry could be easier if just marking abscences. You just back into attendance by subtraction. The attendance table could be as simple as this:

Absences
Absent_ID, autonumber, primary key
Absent_Date, date, date when person was absent
Enroll_ID, number, foreign key to Enrollment

Then you just add a record for each absence, instead of each person for each class no matter if present or absent.

However for that, you would need a start/end date in your class date.

 

[chellebell1689]

Oh, sorry (about the S1 thing).

Wouldn't it be the same thing to just mark present? The way it is currently set up (in Alpha) is they print off a roll sheet with the date of each Sunday for the month. Each Sunday the teacher marks who's present, then Mon I go in and transfer the mark into Alpha via a list of all those on the roll, sorted by class (this also shows the dates for all the Sundays for the month). This is the procedure I'm trying to replicate (only I'd prefer to select the class and then enter the attendance).

The classes don't have start/end dates. It's the same class every Sunday, every month. (But I will be using that set up for when we do home groups! So thanks for that!! XD)

 

[chellebell1689]

For the Enrollment table, how would the relationships go? How does this keep it from looping? Do I need to delete my relation between SSAttendance & SSClass?

 

[plog]

Aren't people more often present than absent? It's less work to record absences then.

 

[chellebell1689]

That's true, but I can't wrap my head around how to take the roll sheet and only mark those absent, and still the the records that the pastor wants. (Member Attendance History, Class Attendance for month/week/etc, and so on.) Not sure if it's the headache, lack of food, or just a duh moment. lol

*Edit*
Also, is it still easier if I have a class of say 30, and 15 show every week, then there's about 5 that show occasionally. For classes like this, is it still easier to just mark those absent?

 

[vbaInet]

Aren't people more often present than absent?

Depends what kind of school you're dealing with But I'm sure that chellebelle's pupils are well behaved

 

[plog]

One of your concerns in your initial post was facilitating data entry. My method allows for the least work possible to arrive at attendance data. That doesn't mean you cant track attendance and absences. Instead of the Absences table I previously proposed, you can use this:

Attendance
Attendance_ID, autonumber, primary key
Attendance_Date, date, date when person was suppose to attend
Enroll_ID, number, foreign key to Enrollment
Attended, Yes/No, determines if person attended (True) or was absent(False)

This just requires you input data for every person for every scheduled class.

 

[chellebell1689]

Ok so I have an enrollment table, members table, ss attend table, and ss class table. They are linked as follows:
Members (one to many) Enrollment
Enrollment (one to many) SSAttend
Enrollment (many to one) SSClass

Is this correct? It looked like that's how you were suggesting the relationships be set up. Even with this set up, the form is not generating names.

Thanks.

P.S.
Sorry for the late reply. I'm now only working Mon & Tues for four hours after I go to school.

 

[plog]

That sounds right. It would be easier to visualize if you posted a screenshot of your relationships view.

As for the form. What form are we talking about? Is it a subform on another form? What fields do you have on it?

 

[chellebell1689]

Here's the picture of the relationships.

As for the form. There will be a main form that the attendance is entered in and a subform that will only show the attendance for a specific person (this sub form will be located in the member's detail form).

The main form will be just the name, date and if they attended with buttons in the footer to sort by class.

The subform will be just the class they were in, the date, and if they were present. Really it will only show one class, unless they help out in the Nursery or some children SS class

 

[chellebell1689]

Ok, so after playing around I think I found the underlining problem. I'm trying to generate a form like the one pictured. (This one was accomplished in my practice db when I added the names to a table for each class.) But in this db I'm trying to pull the names from the member's table and the attendance information from the SSAttend table, which is empty. That's why I keep getting an empty table. Will I have to add the names/EnvNum for the first time?

 

[plog]

I don't think you can accomplish a form like the one pictured. You have dates for column headings. The date value of when they should attend is part of the record itself.

Here's how I would set up your forms to take attendance:

Menu Form - this contains a drop down of all classes, besides it is an input form where you put the date. Beneath those is a button, you click the button and it takes you to the Attendance form for that class on that date.

Attendance Form - This is a form/subform system. The main form is unbound, but holds the Class and Date you submitted in the prior step. The bottom portion is a subform based on Attendance table. On the main form there will be a button that says 'Add Enrollment'. You click that button and it creates a record in the Attendance table for everyone enrolled in that class and it uses the date showing at the top. The sub-form refreshes and you can then enter attendance for that class. Subsequent visits to this class/date allows you to edit any data.

 

[chellebell1689]

Ok I think I understand what you're saying, which sound amazingly easy (just hope my limited knowledge of Access will allow it to be easy). But can you help me set it up? I understand how to do the Menu Form and the date and class would be a temp vars that would be passed to the Attend Form (done that before). For the Attend Form, I would add every member of the class for the first time, but after that would it show the same people? Or would I have to re-add everyone every week?