Daily Student Register and

[Spudkat]

Our charity based org. currently use an Excel spreadsheet which details a
students name, the day and date of the month and a simple " x in the box" to
signify their presence on that day. Absence will leave the box blank.
All very simple, calculating the date from a cell with the date of the 1st
day of the month. A separate sheet is used for each month. This gives us an
overall picture of number of visits for all students during the month. The
sheet is printed, filled in on the day and transferred to the spreadsheet at
the end of the month.

Our benefactors have decided that they require far more detail for the next
round of funding, so I have set up an Access database containing all the
data they require on each individual student.

However.........
do I combine the spreadsheet data with Access
or create multiple tables, one for each month with a field for each day?

I need some of the students details ( e.g. Ethnic Origin, Male/Female, Age
etc.) to be summed up, each month, according to their attendance. For
instance, I have had 10 males of 50 yrs , 8 white, 2 black and 5 females of
36 yrs, 4 white and 1 Asian. I can gather this information using queries on
the tables I have created but how do I create tables for the daily
attendances and sum it all up for the month?
Is there an easier way than the creation of a table for each month
containing a field for every day of that month and then creating a form with
lots of little tick boxes?
Am I blinded by the apparent complexity of the task?

There has to be an easy way - can anyone help, please?

Spudkat

 

[Steve]

You need the following tables:
TblStudent
StudentID
FirstName
MI
LastName
<<StudentDetail Field for each detail that is 1:1 for student. >>

Table for each detail so StudentDetail in TblStudent can be selected from a
list in a combobox.

TblStudentAttendance
StudentAttendanceID
StudentID
AttendanceDate

On any given AttendanceDate, only record students who were in attendance
that date. When determining attendance, for any given AttendanceDate, if a
student is not recorded for that date, he is deemed absent.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John W. Vinson]

I need some of the students details ( e.g. Ethnic Origin, Male/Female, Age
etc.) to be summed up, each month, according to their attendance. For
instance, I have had 10 males of 50 yrs , 8 white, 2 black and 5 females of
36 yrs, 4 white and 1 Asian. I can gather this information using queries on
the tables I have created but how do I create tables for the daily
attendances and sum it all up for the month?
Is there an easier way than the creation of a table for each month
containing a field for every day of that month and then creating a form with
lots of little tick boxes?
Am I blinded by the apparent complexity of the task?

You're suffering the very common syndrome of Spreadsheetitis. An Access Table
may *look* like a spreadsheet, but it is a very different beast!

You need to start by learning about the concept of "normalization". There's a
well-honed set of "best practices" for storing and managing data, formalized
(among other places) by Codd and Date some forty years ago. You can get some
pointers at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

especially the Database Design 101 links on Jeff's webpage.

That said... a thumbnail example. You need one table for each type of Entity
(an Entity is a real-life person, thing, or event). In this case you'll need
at least three tables:

Students
StudentID <Primary Key, a unique field used to reference a student>
LastName
FirstName
Sex
Ethnicity
DateOfBirth <not age, it keeps changing>

Classes
ClassNo <Primary Key>
ClassName
<other info about the class itself>

Attendance
StudentID <who attended a class, link to Students>
ClassNo <what they attended, link to Classes>
AttendanceDate <when they attended>
Comments <e.g. excused absence, ...>

The basic idea is that while spreadsheets are wide and flat, with multiple
columns for things like dates, Tables are tall and thin, with multiple
*records* for repeating values.

John W. Vinson [MVP]

 

[Spudkat]

Thx for the info Steve & John.
Looks like I've got some reading and a lot of work to do.
That 'normalization' thingy rings a bell from college days.......now, if I
can only understand my notes from years ago.....!!!!

Spudkat