Working out the percentage of attendance - Register System

[haz_182]

hey all.




I'm currently struggling with a problem in my database. I'm creating a system with an Access database and visual basic as a front end. I need to work out the percentage of attendance of a student (and after that, students etc).

I have several tables, but the ones I think I will require for this are

Students
Student ID
Name
Session 1
Session 2
Session 3
Session 4
Sessions (ie a class/lession)
Session Name
Start Time
End Time
Session Type
StudAtt (Where the attendance history is stored)
StudentID
Attendance Date
Day Attended
Timestamp

So I am basically struggling with a method of counting the amount of sessions that a student should have attended in the period of time. Each student has 4 sessions per week, but I don't know how to write a query that will count the sessions a student should have been to from the 01/03/2012 to todays date, and from there to work out the % of attendance


I appreciate all your help, thanks!

[orange]

This looks suspiciously like a homework assignment. Your tables are NOT normalized.

Consider your table Students
These fields

Session 1
Session 2
Session 3
Session 4

show two things. They are not related to Student. When you see fields that have incremental suffixes to distinguish one from another, it's a flag for NOT NORMALIZED data.

Your Sessions table does not identify a Day or Dates when a particular Couse/ClassLesson/Session occurs.

In your StudAtt table what are these fields (they sound similar)

Attendance Date
Day Attended

[haz_182]

Hello, thanks for your reply.


This isn't a homework assignment, its for my project.


The four session fields in the student field are lookup values from the session table. I understand that problems could be caused from this, but I'm just trying to get something functioning first.

The session table does have a Session Day field, sorry I must have left that out. This is just a basic text field that contains one of the days from Monday-Friday.

The date attended field in StudAtt contains a date, the Day Attended is a field that is created by a query to find out what day any given date falls on. This field contains days ranging from Monday-friday. This is what I use to compare to the similar field in the session table.

Is there a way that i can count the days from a certain date to the current date? Basically, each student will have four sessions per week. So to figure out the attendance i can maybe count the days, divide it by 7 to get the number of weeks and maybe try and tackle it that way?

[orange]

There is a data model here
http://www.databaseanswers.org/data_...ters/index.htm
that contains more info than you seem to need. That is, some entities and relationships may be beyond the scope of your project. However, the model is generally a real world model.

There are Subjects (Chemistry, Mathematics, History etc).
There are levels (First semester, Second semester, ...)
Then Courses (Calculus 101,Calculus 102..., Physical Chemistry 101...Organic Chemistry 461....)
Then Classes (similar to your Session I think) (Mon 10AM in Room ab50 Teacher 10, Tues 2PM Room PhySc340. Teacher 4....) (I've included Period/Time)
Then Students(StudentId, FirstName, LastName.....)
Then ROSTER (which identifies the Student , Class/Session, and an indicator if the Student attended that specific class/session.)

You can adapt this model, which has been normalized, to meet your needs.

Good luck.

[haz_182]

thanks for the link, that has actually helped a fair bit.

I've got this query so far:

SELECT DateDiff("ww",#9/27/2011#,Date()) AS NumberOfWeeks, [NumberOfWeeks]*4 
AS [number of sessions], Count([attendance date]) AS [sessions attended], ([sessions attended]/[number of sessions]*100)
AS percentage
FROM StudAtt INNER JOIN Students ON StudAtt.Student = Students.StudentID;

This almost gives me exactly what I need. I can add a line that returns the attendance and the percentage for one student, but I want to know if theres a way I can return the individual percentages for all students in this one query?

thanks

currently this query counts the number of attendance dates

[orange]

Please post a jpg of your tables and relationships.

What exactly is the output from your query?