After School Attendance

Hello,

I am attempting to create a database that will track student daily student
attendance at 11 different after school sites. My database needs to perform
the following functions:

Allow me to input daily student attendance for about 800 students. I would
ideally like a form with the students listed in alphabetical order by site
with a drop down yes/no, radio buttons, check box or something that will make
it easy to enter Present/Absent for that many students. I am envisoning a
form for a single date where I can filter student names by shcool site (I
will be receiving sign in sheets from each site, so breaking them up by site
will be the most logical way to enter attendance).

Compile attendance data by school site, and by student. So, the form above
needs to be able to calculate attendance data. Data such as average daily
attendance by site, total attendance program-wide, total attendance per site,
attendance per student, etc. I am pretty sure I can figure this out when the
time comes to create it, but I want to make sure my initial table design
allows for it.

From what I have read so far I am going to need a many-to-many relationship
between my tables. I am thinking I need:

StudentTable
ScheduleDatesTable
SchoolTable
and some sort of junction table that brings all of this together?

I have tried this numerous times but am getting stumped after creating them
all. I go to build a query and it won't display anything. What I need to
know is what do you recommend as far as table design, and what is the next
step in order to actually begin inputting attendance data from the tables? I
know I will need to build some sort of form based on something, a query? the
juntion table? I am not sure after building the tables. Also, I am confused
as to what keys need to be in the junction table? Do you set them as primary
keys, or foreign keys? Can a date/time field be used as a foreign key?
Thank you for taking the time to read my question, and I hope someone can
help.

-Joe

On Wed, 9 Jul 2008 12:22:00 -0700, joestros
wrote:

Hello,

I am attempting to create a database that will track student daily student
attendance at 11 different after school sites. My database needs to perform
the following functions:

Allow me to input daily student attendance for about 800 students. I would
ideally like a form with the students listed in alphabetical order by site
with a drop down yes/no, radio buttons, check box or something that will make
it easy to enter Present/Absent for that many students. I am envisoning a
form for a single date where I can filter student names by shcool site (I
will be receiving sign in sheets from each site, so breaking them up by site
will be the most logical way to enter attendance).

Compile attendance data by school site, and by student. So, the form above
needs to be able to calculate attendance data. Data such as average daily
attendance by site, total attendance program-wide, total attendance per site,
attendance per student, etc. I am pretty sure I can figure this out when the
time comes to create it, but I want to make sure my initial table design
allows for it.

From what I have read so far I am going to need a many-to-many relationship
between my tables. I am thinking I need:

StudentTable
ScheduleDatesTable
SchoolTable
and some sort of junction table that brings all of this together?

I have tried this numerous times but am getting stumped after creating them
all. I go to build a query and it won't display anything. What I need to
know is what do you recommend as far as table design, and what is the next
step in order to actually begin inputting attendance data from the tables? I
know I will need to build some sort of form based on something, a query? the
juntion table? I am not sure after building the tables. Also, I am confused
as to what keys need to be in the junction table? Do you set them as primary
keys, or foreign keys? Can a date/time field be used as a foreign key?
Thank you for taking the time to read my question, and I hope someone can
help.

-Joe

First off you should realize that you will have 800 attendance records for each
school day. Each day you will need to generate an attendance record for each of
the 800 students, probably with a default value of present. Then it is just a
matter of the teacher at each school pulling up her roster and changing the
present to absent for her missing stuents.

StudentAttendance
student_num
school_num
calendar_date
attendance_code
PRIMARY KEY (student_num,school_num,calendar_date)

You probably need a calendar table with every school day date.

Thank you for the quick reply Michael. I'm a little confused as to what you
meant by this:

StudentAttendance
student_num
school_num
calendar_date
attendance_code
PRIMARY KEY (student_num,school_num,calendar_date)

Does this mean StudentAttendance is a junction table? Are student_num,
school_num, calendar_num, all primary keys from their respective tables? Is
attendance_code a field in the junction table?

I understand there will be 800 attendance records per day. What I am trying
to avoid is having to click on 800 students records each day, click to fill
in an attendance date, and then click again to mark absent (that would be
2400 mouse clicks per day)! We currently use an expensive database service
that allows us to choose a date and it brings up a list of students with
radio buttons that default to present, and we go through and mark the absent
students. I understand radio buttons are probably not the way to go, but is
there a way to create a form from these tables that will allow a list of
students to display with the attendance_code selector next to each one? And
after selecting the value it would update their attendance record?

Thank you again for your help.

-Joe

"Michael Gramelspacher" wrote:

On Wed, 9 Jul 2008 12:22:00 -0700, joestros
wrote:

Hello,

I am attempting to create a database that will track student daily student
attendance at 11 different after school sites. My database needs to perform
the following functions:

Allow me to input daily student attendance for about 800 students. I would
ideally like a form with the students listed in alphabetical order by site
with a drop down yes/no, radio buttons, check box or something that will make
it easy to enter Present/Absent for that many students. I am envisoning a
form for a single date where I can filter student names by shcool site (I
will be receiving sign in sheets from each site, so breaking them up by site
will be the most logical way to enter attendance).

Compile attendance data by school site, and by student. So, the form above
needs to be able to calculate attendance data. Data such as average daily
attendance by site, total attendance program-wide, total attendance per site,
attendance per student, etc. I am pretty sure I can figure this out when the
time comes to create it, but I want to make sure my initial table design
allows for it.

From what I have read so far I am going to need a many-to-many relationship
between my tables. I am thinking I need:

StudentTable
ScheduleDatesTable
SchoolTable
and some sort of junction table that brings all of this together?

I have tried this numerous times but am getting stumped after creating them
all. I go to build a query and it won't display anything. What I need to
know is what do you recommend as far as table design, and what is the next
step in order to actually begin inputting attendance data from the tables? I
know I will need to build some sort of form based on something, a query? the
juntion table? I am not sure after building the tables. Also, I am confused
as to what keys need to be in the junction table? Do you set them as primary
keys, or foreign keys? Can a date/time field be used as a foreign key?
Thank you for taking the time to read my question, and I hope someone can
help.

-Joe

First off you should realize that you will have 800 attendance records for each
school day. Each day you will need to generate an attendance record for each of
the 800 students, probably with a default value of present. Then it is just a
matter of the teacher at each school pulling up her roster and changing the
present to absent for her missing stuents.

StudentAttendance
student_num
school_num
calendar_date
attendance_code
PRIMARY KEY (student_num,school_num,calendar_date)

You probably need a calendar table with every school day date.

Joe,

I provide help with Access, Excel and Word applications for a very
reasonable fee. I could build this database for you for a very modest fee.
If you would like my help, cotact me at .

Steve



"joestros" wrote in message
...
Hello,

I am attempting to create a database that will track student daily student
attendance at 11 different after school sites. My database needs to
perform
the following functions:

Allow me to input daily student attendance for about 800 students. I
would
ideally like a form with the students listed in alphabetical order by site
with a drop down yes/no, radio buttons, check box or something that will
make
it easy to enter Present/Absent for that many students. I am envisoning a
form for a single date where I can filter student names by shcool site (I
will be receiving sign in sheets from each site, so breaking them up by
site
will be the most logical way to enter attendance).

Compile attendance data by school site, and by student. So, the form
above
needs to be able to calculate attendance data. Data such as average daily
attendance by site, total attendance program-wide, total attendance per
site,
attendance per student, etc. I am pretty sure I can figure this out when
the
time comes to create it, but I want to make sure my initial table design
allows for it.

From what I have read so far I am going to need a many-to-many
relationship
between my tables. I am thinking I need:

StudentTable
ScheduleDatesTable
SchoolTable
and some sort of junction table that brings all of this together?

I have tried this numerous times but am getting stumped after creating
them
all. I go to build a query and it won't display anything. What I need to
know is what do you recommend as far as table design, and what is the next
step in order to actually begin inputting attendance data from the tables?
I
know I will need to build some sort of form based on something, a query?
the
juntion table? I am not sure after building the tables. Also, I am
confused
as to what keys need to be in the junction table? Do you set them as
primary
keys, or foreign keys? Can a date/time field be used as a foreign key?
Thank you for taking the time to read my question, and I hope someone can
help.

-Joe

On Wed, 9 Jul 2008 13:38:32 -0700, joestros
wrote:

Thank you for the quick reply Michael. I'm a little confused as to what you
meant by this:

StudentAttendance
student_num
school_num
calendar_date
attendance_code
PRIMARY KEY (student_num,school_num,calendar_date)

Does this mean StudentAttendance is a junction table? Are student_num,
school_num, calendar_num, all primary keys from their respective tables? Is
attendance_code a field in the junction table?

I understand there will be 800 attendance records per day. What I am trying
to avoid is having to click on 800 students records each day, click to fill
in an attendance date, and then click again to mark absent (that would be
2400 mouse clicks per day)! We currently use an expensive database service
that allows us to choose a date and it brings up a list of students with
radio buttons that default to present, and we go through and mark the absent
students. I understand radio buttons are probably not the way to go, but is
there a way to create a form from these tables that will allow a list of
students to display with the attendance_code selector next to each one? And
after selecting the value it would update their attendance record?

Thank you again for your help.

-Joe

StudentAttendance would be a juntion table. I am a bit confused. It seems like
you are saying that you keep attendance with another applicaion which generates
an attendance roster every day. You then just have to change the presents to
absences for those few students who are absent. That how it would also work
with Access. Why do you need the second Access program? Maybe the roster you
are describing is just for the normal school day. It that it?

"Michael Gramelspacher" wrote:

On Wed, 9 Jul 2008 12:22:00 -0700, joestros
wrote:

Hello,

I am attempting to create a database that will track student daily student
attendance at 11 different after school sites. My database needs to perform
the following functions:

Allow me to input daily student attendance for about 800 students. I would
ideally like a form with the students listed in alphabetical order by site
with a drop down yes/no, radio buttons, check box or something that will make
it easy to enter Present/Absent for that many students. I am envisoning a
form for a single date where I can filter student names by shcool site (I
will be receiving sign in sheets from each site, so breaking them up by site
will be the most logical way to enter attendance).

Compile attendance data by school site, and by student. So, the form above
needs to be able to calculate attendance data. Data such as average daily
attendance by site, total attendance program-wide, total attendance per site,
attendance per student, etc. I am pretty sure I can figure this out when the
time comes to create it, but I want to make sure my initial table design
allows for it.

From what I have read so far I am going to need a many-to-many relationship
between my tables. I am thinking I need:

StudentTable
ScheduleDatesTable
SchoolTable
and some sort of junction table that brings all of this together?

I have tried this numerous times but am getting stumped after creating them
all. I go to build a query and it won't display anything. What I need to
know is what do you recommend as far as table design, and what is the next
step in order to actually begin inputting attendance data from the tables? I
know I will need to build some sort of form based on something, a query? the
juntion table? I am not sure after building the tables. Also, I am confused
as to what keys need to be in the junction table? Do you set them as primary
keys, or foreign keys? Can a date/time field be used as a foreign key?
Thank you for taking the time to read my question, and I hope someone can
help.

-Joe

First off you should realize that you will have 800 attendance records for each
school day. Each day you will need to generate an attendance record for each of
the 800 students, probably with a default value of present. Then it is just a
matter of the teacher at each school pulling up her roster and changing the
present to absent for her missing stuents.

StudentAttendance
student_num
school_num
calendar_date
attendance_code
PRIMARY KEY (student_num,school_num,calendar_date)

You probably need a calendar table with every school day date.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualifed people who would be delighted to help. Of
course, little stevie is not one of them. He has a long history of annoying
these newsgroups with his solicitation, providing wrong answers and a lot of
other questionable activity.

John...

"Steve" wrote in message
m...

I provide help with Access, Excel and Word applications for a very
reasonable fee. I could build this database for you for a very modest fee.
If you would like my help, cotact Roberta and complain of
little stevie.

Steve

--
ARRRGGGHHHHHHHH !!!!!!!!
--
Hey Mark/Steve !!
Did you get lots of new customers from the groups lately ??
How many developers still work for you ??

This is to inform 'newbees' here about PCD' Steve:
http://home.tiscali.nl/arracom/whoissteve.html (Earthlink and SuperNews kicked him out ?? == updated 'abuse-reporting')
Until now 6000+ pageloads, 4000+ first-time visitors

*********************************
If anyone wants to help us getting rid of Steve ??
(appropriate action will follow when there are enough complaints)
*********************************

Arno R

Michael,

We are currently using an web-based service that is very costly. We are
looking to save money, so they handed me the task of creating a student
attendance database. Basically I will be building a database similar to the
web-based service we currently use, although it won't be online. The way
they have it setup on the current system makes it very easy to enter
attendance. The managers print out a sign in sheet each day from the system,
then use the sign in sheet to enter absent/present into their site. The
students names from each site are displayed on one form with radio buttons
for present/absent next to each name. This data is then saved for each day
of attendance. We can then run reports based on student name, site name,
program wide data, etc.

Is there a way to set up a form in Access in a similar fashion? Since I
will be the one entering attendance data each day I would like something easy
as described above. I would like to not have to click on 800 records each
day. I understand I will have that many records each day, but if I can avoid
having to scroll through each student one-by-one and mark presernt/absent
that would be great. I want to make sure I have my tables set up correctly
as well.

Thanks for your help so far.

-Joe

"Michael Gramelspacher" wrote:

On Wed, 9 Jul 2008 13:38:32 -0700, joestros
wrote:

Thank you for the quick reply Michael. I'm a little confused as to what you
meant by this:

StudentAttendance
student_num
school_num
calendar_date
attendance_code
PRIMARY KEY (student_num,school_num,calendar_date)

Does this mean StudentAttendance is a junction table? Are student_num,
school_num, calendar_num, all primary keys from their respective tables? Is
attendance_code a field in the junction table?

I understand there will be 800 attendance records per day. What I am trying
to avoid is having to click on 800 students records each day, click to fill
in an attendance date, and then click again to mark absent (that would be
2400 mouse clicks per day)! We currently use an expensive database service
that allows us to choose a date and it brings up a list of students with
radio buttons that default to present, and we go through and mark the absent
students. I understand radio buttons are probably not the way to go, but is
there a way to create a form from these tables that will allow a list of
students to display with the attendance_code selector next to each one? And
after selecting the value it would update their attendance record?

Thank you again for your help.

-Joe

StudentAttendance would be a juntion table. I am a bit confused. It seems like
you are saying that you keep attendance with another applicaion which generates
an attendance roster every day. You then just have to change the presents to
absences for those few students who are absent. That how it would also work
with Access. Why do you need the second Access program? Maybe the roster you
are describing is just for the normal school day. It that it?

"Michael Gramelspacher" wrote:

On Wed, 9 Jul 2008 12:22:00 -0700, joestros
wrote:

Hello,

I am attempting to create a database that will track student daily student
attendance at 11 different after school sites. My database needs to perform
the following functions:

Allow me to input daily student attendance for about 800 students. I would
ideally like a form with the students listed in alphabetical order by site
with a drop down yes/no, radio buttons, check box or something that will make
it easy to enter Present/Absent for that many students. I am envisoning a
form for a single date where I can filter student names by shcool site (I
will be receiving sign in sheets from each site, so breaking them up by site
will be the most logical way to enter attendance).

Compile attendance data by school site, and by student. So, the form above
needs to be able to calculate attendance data. Data such as average daily
attendance by site, total attendance program-wide, total attendance per site,
attendance per student, etc. I am pretty sure I can figure this out when the
time comes to create it, but I want to make sure my initial table design
allows for it.

From what I have read so far I am going to need a many-to-many relationship
between my tables. I am thinking I need:

StudentTable
ScheduleDatesTable
SchoolTable
and some sort of junction table that brings all of this together?

I have tried this numerous times but am getting stumped after creating them
all. I go to build a query and it won't display anything. What I need to
know is what do you recommend as far as table design, and what is the next
step in order to actually begin inputting attendance data from the tables? I
know I will need to build some sort of form based on something, a query? the
juntion table? I am not sure after building the tables. Also, I am confused
as to what keys need to be in the junction table? Do you set them as primary
keys, or foreign keys? Can a date/time field be used as a foreign key?
Thank you for taking the time to read my question, and I hope someone can
help.

-Joe

First off you should realize that you will have 800 attendance records for each
school day. Each day you will need to generate an attendance record for each of
the 800 students, probably with a default value of present. Then it is just a
matter of the teacher at each school pulling up her roster and changing the
present to absent for her missing stuents.

StudentAttendance
student_num
school_num
calendar_date
attendance_code
PRIMARY KEY (student_num,school_num,calendar_date)

You probably need a calendar table with every school day date.

On Thu, 10 Jul 2008 08:44:01 -0700, joestros
wrote:

Michael,

We are currently using an web-based service that is very costly. We are
looking to save money, so they handed me the task of creating a student
attendance database. Basically I will be building a database similar to the
web-based service we currently use, although it won't be online. The way
they have it setup on the current system makes it very easy to enter
attendance. The managers print out a sign in sheet each day from the system,
then use the sign in sheet to enter absent/present into their site. The
students names from each site are displayed on one form with radio buttons
for present/absent next to each name. This data is then saved for each day
of attendance. We can then run reports based on student name, site name,
program wide data, etc.

Is there a way to set up a form in Access in a similar fashion? Since I
will be the one entering attendance data each day I would like something easy
as described above. I would like to not have to click on 800 records each
day. I understand I will have that many records each day, but if I can avoid
having to scroll through each student one-by-one and mark presernt/absent
that would be great. I want to make sure I have my tables set up correctly
as well.

Thanks for your help so far.

-Joe

"Michael Gramelspacher" wrote:

On Wed, 9 Jul 2008 13:38:32 -0700, joestros
wrote:

Thank you for the quick reply Michael. I'm a little confused as to what you
meant by this:

StudentAttendance
student_num
school_num
calendar_date
attendance_code
PRIMARY KEY (student_num,school_num,calendar_date)

Does this mean StudentAttendance is a junction table? Are student_num,
school_num, calendar_num, all primary keys from their respective tables? Is
attendance_code a field in the junction table?

I understand there will be 800 attendance records per day. What I am trying
to avoid is having to click on 800 students records each day, click to fill
in an attendance date, and then click again to mark absent (that would be
2400 mouse clicks per day)! We currently use an expensive database service
that allows us to choose a date and it brings up a list of students with
radio buttons that default to present, and we go through and mark the absent
students. I understand radio buttons are probably not the way to go, but is
there a way to create a form from these tables that will allow a list of
students to display with the attendance_code selector next to each one? And
after selecting the value it would update their attendance record?

Thank you again for your help.

-Joe

StudentAttendance would be a juntion table. I am a bit confused. It seems like
you are saying that you keep attendance with another applicaion which generates
an attendance roster every day. You then just have to change the presents to
absences for those few students who are absent. That how it would also work
with Access. Why do you need the second Access program? Maybe the roster you
are describing is just for the normal school day. It that it?

"Michael Gramelspacher" wrote:

On Wed, 9 Jul 2008 12:22:00 -0700, joestros
wrote:

Hello,

I am attempting to create a database that will track student daily student
attendance at 11 different after school sites. My database needs to perform
the following functions:

Allow me to input daily student attendance for about 800 students. I would
ideally like a form with the students listed in alphabetical order by site
with a drop down yes/no, radio buttons, check box or something that will make
it easy to enter Present/Absent for that many students. I am envisoning a
form for a single date where I can filter student names by shcool site (I
will be receiving sign in sheets from each site, so breaking them up by site
will be the most logical way to enter attendance).

Compile attendance data by school site, and by student. So, the form above
needs to be able to calculate attendance data. Data such as average daily
attendance by site, total attendance program-wide, total attendance per site,
attendance per student, etc. I am pretty sure I can figure this out when the
time comes to create it, but I want to make sure my initial table design
allows for it.

From what I have read so far I am going to need a many-to-many relationship
between my tables. I am thinking I need:

StudentTable
ScheduleDatesTable
SchoolTable
and some sort of junction table that brings all of this together?

I have tried this numerous times but am getting stumped after creating them
all. I go to build a query and it won't display anything. What I need to
know is what do you recommend as far as table design, and what is the next
step in order to actually begin inputting attendance data from the tables? I
know I will need to build some sort of form based on something, a query? the
juntion table? I am not sure after building the tables. Also, I am confused
as to what keys need to be in the junction table? Do you set them as primary
keys, or foreign keys? Can a date/time field be used as a foreign key?
Thank you for taking the time to read my question, and I hope someone can
help.

-Joe

First off you should realize that you will have 800 attendance records for each
school day. Each day you will need to generate an attendance record for each of
the 800 students, probably with a default value of present. Then it is just a
matter of the teacher at each school pulling up her roster and changing the
present to absent for her missing stuents.

StudentAttendance
student_num
school_num
calendar_date
attendance_code
PRIMARY KEY (student_num,school_num,calendar_date)

You probably need a calendar table with every school day date.



You need a Students table. (student_num)

You need a Schools table. (school_num)

You need a relationship table for Students and Schools.
lets call it StudentEnrolments.
student_num, school_num, school_year, semester_num

You need a StudentAttendance table, i.e.,
All fields from StudentEnrolments plus calendar_date and attendance_code .

I would have a main table based on Schools and a subform based on
StudentAttendance. I would have a combobox or calendar control in the form
header to pick the date to see.

I would have a command button in the header to run a query to generate a roster
for today, if none exits already.

I think this is the general idea. You can have a radio button to mark
attendance in the subform or an option group with two buttons, present (default)
and absent. I would say it is a matter of preference.

The hard part is managing enrolments and disenrolments to keep your student
table current.

Ok, I think I am getting it. Now do I need to create a table for
calendar_date and another for attendance_code? It sounds like there will be
two junction tables? StudentEnrollments and StudentAttendance?

The calendar date is the hardest part for me to understand since all of the
students and schools will be sharing the calendar date. If I use a calendar
control where will this date get stored for running queries later?

"Michael Gramelspacher" wrote:

On Thu, 10 Jul 2008 08:44:01 -0700, joestros
wrote:

Michael,

We are currently using an web-based service that is very costly. We are
looking to save money, so they handed me the task of creating a student
attendance database. Basically I will be building a database similar to the
web-based service we currently use, although it won't be online. The way
they have it setup on the current system makes it very easy to enter
attendance. The managers print out a sign in sheet each day from the system,
then use the sign in sheet to enter absent/present into their site. The
students names from each site are displayed on one form with radio buttons
for present/absent next to each name. This data is then saved for each day
of attendance. We can then run reports based on student name, site name,
program wide data, etc.

Is there a way to set up a form in Access in a similar fashion? Since I
will be the one entering attendance data each day I would like something easy
as described above. I would like to not have to click on 800 records each
day. I understand I will have that many records each day, but if I can avoid
having to scroll through each student one-by-one and mark presernt/absent
that would be great. I want to make sure I have my tables set up correctly
as well.

Thanks for your help so far.

-Joe

"Michael Gramelspacher" wrote:

On Wed, 9 Jul 2008 13:38:32 -0700, joestros
wrote:

Thank you for the quick reply Michael. I'm a little confused as to what you
meant by this:

StudentAttendance
student_num
school_num
calendar_date
attendance_code
PRIMARY KEY (student_num,school_num,calendar_date)

Does this mean StudentAttendance is a junction table? Are student_num,
school_num, calendar_num, all primary keys from their respective tables? Is
attendance_code a field in the junction table?

I understand there will be 800 attendance records per day. What I am trying
to avoid is having to click on 800 students records each day, click to fill
in an attendance date, and then click again to mark absent (that would be
2400 mouse clicks per day)! We currently use an expensive database service
that allows us to choose a date and it brings up a list of students with
radio buttons that default to present, and we go through and mark the absent
students. I understand radio buttons are probably not the way to go, but is
there a way to create a form from these tables that will allow a list of
students to display with the attendance_code selector next to each one? And
after selecting the value it would update their attendance record?

Thank you again for your help.

-Joe

StudentAttendance would be a juntion table. I am a bit confused. It seems like
you are saying that you keep attendance with another applicaion which generates
an attendance roster every day. You then just have to change the presents to
absences for those few students who are absent. That how it would also work
with Access. Why do you need the second Access program? Maybe the roster you
are describing is just for the normal school day. It that it?

"Michael Gramelspacher" wrote:

On Wed, 9 Jul 2008 12:22:00 -0700, joestros
wrote:

Hello,

I am attempting to create a database that will track student daily student
attendance at 11 different after school sites. My database needs to perform
the following functions:

Allow me to input daily student attendance for about 800 students. I would
ideally like a form with the students listed in alphabetical order by site
with a drop down yes/no, radio buttons, check box or something that will make
it easy to enter Present/Absent for that many students. I am envisoning a
form for a single date where I can filter student names by shcool site (I
will be receiving sign in sheets from each site, so breaking them up by site
will be the most logical way to enter attendance).

Compile attendance data by school site, and by student. So, the form above
needs to be able to calculate attendance data. Data such as average daily
attendance by site, total attendance program-wide, total attendance per site,
attendance per student, etc. I am pretty sure I can figure this out when the
time comes to create it, but I want to make sure my initial table design
allows for it.

From what I have read so far I am going to need a many-to-many relationship
between my tables. I am thinking I need:

StudentTable
ScheduleDatesTable
SchoolTable
and some sort of junction table that brings all of this together?

I have tried this numerous times but am getting stumped after creating them
all. I go to build a query and it won't display anything. What I need to
know is what do you recommend as far as table design, and what is the next
step in order to actually begin inputting attendance data from the tables? I
know I will need to build some sort of form based on something, a query? the
juntion table? I am not sure after building the tables. Also, I am confused
as to what keys need to be in the junction table? Do you set them as primary
keys, or foreign keys? Can a date/time field be used as a foreign key?
Thank you for taking the time to read my question, and I hope someone can
help.

-Joe

First off you should realize that you will have 800 attendance records for each
school day. Each day you will need to generate an attendance record for each of
the 800 students, probably with a default value of present. Then it is just a
matter of the teacher at each school pulling up her roster and changing the
present to absent for her missing stuents.

StudentAttendance
student_num
school_num
calendar_date
attendance_code
PRIMARY KEY (student_num,school_num,calendar_date)

You probably need a calendar table with every school day date.



You need a Students table. (student_num)

You need a Schools table. (school_num)

You need a relationship table for Students and Schools.
lets call it StudentEnrolments.
student_num, school_num, school_year, semester_num

You need a StudentAttendance table, i.e.,
All fields from StudentEnrolments plus calendar_date and attendance_code .

I would have a main table based on Schools and a subform based on
StudentAttendance. I would have a combobox or calendar control in the form
header to pick the date to see.

I would have a command button in the header to run a query to generate a roster
for today, if none exits already.

I think this is the general idea. You can have a radio button to mark
attendance in the subform or an option group with two buttons, present (default)
and absent. I would say it is a matter of preference.

The hard part is managing enrolments and disenrolments to keep your student
table current.