In over my head

[TylerB]

Hi all,
I have been tasked with developing a database for a company through my college's MIS department. I have utilized these forums throughout the past semester to help me with my development of the database, however at this point; I have reached a level of complexity with my database that is beyond my very limited experience level. I am not quite sure of the best way to proceed, so it is limiting my ability to search for the appropriate keywords or topics. I have been through the forums and am unable to find what I am looking for so I am forced to make a thread. Please bear with me while I attempt to explain my two major problems.

1. The database is for an after school program that will use the system to track their attendance, student and staff info, emergency contact information.
The attendance is broken down into 2 types. A general attendance, and an activity attendance. From there, I have 2 types of activity attendance, A workshop, and Club XL(Physical activities).
When a student attends the program, they will earn one student quality point.


From there a student will have the option to attend the workshop, which is worth one point, along with the Club XL, which is also worth one point.
The maximum point value a student would be able to earn is 3 points per day if they participated in all three activities.
The client needs to be able to calculate the total number of quality points a student earns over various dates. However, the way that I have designed the database, and my lack of knowledge is preventing me from understanding how to attack this problem. When I sum the point values with my query It will not include the general attendance and only count the activity attendance. (This is under the Quality Points Query)
2.
The Client has requested a report that will take the average daily general attendance, the number of students that have attended, and the number of possible days within the specified date parameters. This will work as an end of month report. I am able to create queries that pull the parameters from a popup form but I do not have a knowledge to perform the appropriate calculations. ( This is under the End of Month Query) (The student Jeffery Dahmer should have 3 points but will only register 2)

I am hoping that an extremely nice person would be willing to take a look through my database and point me in the proper direction because at this point I am in over my head. I do realize that the way the database was designed initially was not the most efficient but at this point I just need to make it work. Thank you for reading my novel. Any help or advice on where to go from here would be greatly appreciated.

[June7]

Problem 1:
Rename Quality Points Query to Activity Points Query, remove the General Attendance table from this query, add the Student ID field, can exclude the names.
Build another query of General Attendance that will count the students grouped by Student ID, include Student ID field.
Build another query that joins these two queries to the Student Information table linking on the Student ID.
Any date filtering criteria will have to be same in both of the aggregate queries.

Problem 2:
Build a report, not a query, to accomplish this. It is simple to calculate the number of calendar days between two dates, excluding weekends and holidays is much more complicated. It will require a custom function. I found code (doesn't account for holidays) for this at http://support.microsoft.com/kb/97757

[TylerB]

June,

I cant say thank you enough! What you did was exactly what I needed to get going on the right track. Thank you for bearing with me and sorting through my db.


Can I ask why you divided the total attendance count by the total days and not the number of work days? I cant seem to get the average to work correctly.
Tyler