Making my volunteer database something useful (1 Viewer)

[Sempaliscious]

Greetings.

Sorry in advance for the overly long post describing the situation.

I have been searching this forum for the past 90 minutes or so for a solution to my particular woes but I can't seem to find one. It's a particularly curious situation (that I'm sure is simple to many people but complicated to a noob like me). This is the situation:

I have inherited a volunteer database that has 3 tables - a volunteer table (has 30+ fields for all the details of the volunteer, held together with an ID primary key), a role table (which has the 8 different roles that a volunteer can take on) and a location table (which lists all the locations they can volunteer at).

We offer various training to our volunteers (3 levels, IT training, Child protection training etc etc). Some courses run just 1 day and some run for 6 days (over a few weeks).

The genius that designed this database has just kept adding fields to the volunteer table so now there are "Course I", "Course II" etc etc fields which are populated by a mix of "yes"s or the year they last completed the course.

This seems really clumsy to me. We end up having to keep all our paper attendance rolls because we require, for example, people to attend 5 out of 6 days of a 6 day course. And there is no function in the database to record this. And, as we add more courses, it is just getting more and more ridiculous (there would be close to 50 fields now in the volunteer table)

What I really need is to be able to have just the volunteer info in the volunteer table (who they are, what role they have and where they help out) and then create a courses table where I can add "Level I Training" and the year "2014", for example, select the location of the training, say how long the course is and track attendance.

The hope being that I can run a query at the end of the year of who completed what courses and issue certificates as appropriate.

Is this a complicated venture? It doesn't need to be able to record the attendance info as the course goes along. Even if it was able to just receive the info in one hit at the end (a form that has the course name and year, student names and then yes/no for each week)

As I said, I'm a complete noob. I've played with access over the years, creating tables and simple forms to search etc but this relational stuff is above my understanding.

Can anyone point me in the right direction or at least get me started? I've seen a data model posted by others at databaseanswers (student rosters) and it is far too complicated for what I need. I think I can kinda see the structure but I don't know how to enter data once I've created what I've visualised.

Ignoring the role and location they serve tables, I can imagine it would look kinda like this?

tblVolunteer -
VolunteerID (PK)
Name etc etc etc

tblCourseInfo -
CourseID (PK)
NameofCourse
YearofCourse
LocationOffered (I'd have a table with the different places we offer courses)
(and any other extra fields describing the course, length, etc)

tblWeek
weekid (PK)
WeekName (e.g. "Week 1" or perhaps just "Attendance" if it's a 1 day course)
CourseID (FK)
VolunteerID (FK)
Attended_YN

Presumably, I could make a query that shows all the names and weekly attendance of Volunteers that attended Level I in 2013 @ the Illawarra centre across 6 weeks?

The next question is simply - if this is the right path, once i've created these tables with the relationships in place, is it just a matter of creating a form to enter in the details of a course and then creating a form to track the attendance? Where I mentally get stuck is simply how does the database know that someone is enrolled in the course? With this structure, is there a way, once I've created all the course details to bring up a form that has the course name at the top, a list of names (which I can add to) and then their weekly attendance?

Hopefully the length of my description actually helps and not hinders. :banghead:

Any help you guys can offer would be most appreciated. I really want to understand how to do this.

Thanks again,
Sempaliscious

 

[Rx_]

there are "Course I", "Course II" etc etc fields
This is a common mistake to create multiple fields for each description for an entity of Course.

Suggest that you visit the link of Video Tutorials on this forum.
At this time, it doesn't contain a video of Referential Integrity (RI). RI is key to any database design including MS Access.
Instead of multiple columns with course names, there needs to be one table of Courses. Google for Referential Integrity video, there should probably be many great ones.

Once that is done, this will require all forms, reports, and queries to be re-written. Yes, RI is the foundation for a database.

B.T.W. this is not an uncommon thing to do when inheriting a database. Data organization doesn't just happen by itself.

Hope you learn some RI, then come back to ask some specific questions.
Regards