presence and absence

[Spier2vb]

Hello, I am in the process of creating my database and I was looking for some
guidance. I have created three tables
TblGroups
GroupID PK
GroupName

TblStudent
StudentID PK
Name
GroupID

TblAttendance
Date
Presence Yes/No
Absence Yes/No
StudentID

I need to be able to design form dependent on date field in TblAttendance
In other words i need design form depend on date field and group name compo
box and subform include student id and name and presence and absence, when i
add new record type current date and i selected group show me in subform
student id and name and presence and absence, i tried to do it but i fail
Any help is appreciated.

Thanks,
Spider

 

[PvdG42]

Hello, I am in the process of creating my database and I was looking for
some
guidance. I have created three tables
TblGroups
GroupID PK
GroupName

TblStudent
StudentID PK
Name
GroupID

TblAttendance
Date
Presence Yes/No
Absence Yes/No
StudentID

I need to be able to design form dependent on date field in TblAttendance
In other words i need design form depend on date field and group name
compo
box and subform include student id and name and presence and absence, when
i
add new record type current date and i selected group show me in subform
student id and name and presence and absence, i tried to do it but i fail
Any help is appreciated.

Thanks,
Spider

Before worrying about a subform, take another look at your database design.
Why *two* Yes/No fields in tblAttendance to indicate if a student attended
on a given date? Is not "present" the antithesis of "absent"? IOW, if
Presence is true, Absence is false by definition and vice versa, correct?

 

[Spier2vb]

what your suggests
Please help!

 

[Gina Whipp]

Spier2vb,

Right off the bat I see you have used some Reserved Words as field names,
ie: Date and Name, Access doesn't like that. So you need to fix that,
here's a complete list...

http://allenbrowne.com/AppIssueBadWord.html

For typical naming conventions see...

http://www.granite.ab.ca/access/tablefieldnaming.htm

http://www.regina-whipp.com/index_files/NamingConventions.htm

You have explained that you tried something and it didn't work. You have
shown us your table set up but I'm unclear of how your forms are set up. I
also do not understand what tblGroups is for? Because if the Students are
in the Group and you want to assign values based on what group they are then
you are missing a joiner table...

tblStudentGroups
sgStudentID (FK)
sgGroupID (FK)

Now, did you want the Attendence part to prefill automatically with the
Student, so all you have to do is check boxes?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Spier2vb]

You're awesome i changed tables to be
Classes: ClassID (PK), ClassName, StudentID (FK)
Students: StudentID (PK), FirstName, LastName
Enrollments: ClassID (FK), StudentID (FK)
Attendance: AttendanceID, AttendanceDate, StudentID (FK), Present (yes/no)
Now i would like to be able to keep track of their attendence,
Thanks for your help

And I'm going to want my form based on the Classes table, then have a
subform based on the Attendence table....?? This is where I get lost,
because I want it to display the ClassName (combobox) where I want to display
the classes , and then list all the students that are signed up for that
class, then have a Date field to distinguish between classes, then next to
the each students' name have a checkbox to verify if they were there on that
particular class. Can you help me out with this last little feature of my
database??

 

[Gina Whipp]

Spier2vb,

How are you assigning Students to Classes now? I would think you want to
join the your Student Classes table to your Attendence table where you could
just then check a box. I also wonder about using the Date to distinguish
between Class. Each Class should really have an ID and that is what you
would use. (You must have more then one Class and any given day?)

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm