Team Automation

[Nathan Pell]

Hello All:

I have a database in MS Access to keep track of all of our "stuff" that goes on with the team. I would like to add a "feature" that will keep track of our member's attendance.

I thought that I would post this question to see how other teams do this. We are growing quite quickly, so I would really like to have this information in a database.

Any ideas?

Thanks!
Nathan Pell
1083 - "The Crew"

[seanwitte]

It was too late to do it last year, but I played around with one of those free Radio Shack barcode scanners last year with the intention of giving out coded ID's that could be scanned on entrace/exit to keep track of the kids' time. The downside is that you need a PC dedicated to this.

There are instructions on the web for hacking the CueCat to convert it to a keyboard wedge. www.cexx.com/cuecat.htm When you scan the bar code it acts just like you typed it in on the keyboard. There is a free font available so you can create a report in your Access database to print labels with names and coded IDs. The hack just involves soldering a jumper to the PCB inside the cat.

[Nathan Pell]

That is great, but what I really was interested in is what is the best way to setup a database system in the first place.

I have a MS Access database right now with all the name, address, and that kind of information. I was just thinking of adding another table, but wasn't sure how to link it to the rest to make sure the database is still a relational database.

Any ideas?

Nathan

[seanwitte]

Sure, sorry, I misunderstood.

Say you have a table holding participant information called PERSON. That table should have an AutoNumber field named PERSON_ID that is the primary key. Its a number that automatically increments each time you add a record.

Each time you have a meeting or an event you probably want to keep track of who was there. Create another table named EVENT with an AutoNumber primary key named EVENT_ID. EVENT may have some fields like:

event_begin, datetime
event_end, datetime
event_type (meeting, regional, fundraiser, etc..)
description

In order to keep track of who attended each event, you would create a table to link the PERSON table to the EVENT table. This is called an Associative Relationship. Call it PERSON_EVENT, and it will contain the two keys from the parent tables, PERSON_ID and EVENT_ID. You can also add other metadata like:

person_id, long int.
event_id, long int.
time_in, datetime
time_out, datetime
role_type (pit crew, scouting, electrical, mechanical, etc.)

The person_id and event_id are called Foreign Keys, since their value is the key from another table. When you log a person into an event you add a record to PERSON_EVENT with the person's PERSON_ID value and the event's EVENT_ID.

To get the list of people who attended an event you just join PERSON and EVENT with PERSON_EVENT by linking on person_id and event_id.

[Nathan Pell]

Wow... thanks!

that is exactly what I was looking for.

I am going to give this a try!

Thanks
Nathan Pell

[Nate Smith]

Something I actually had a couple years ago for the team I was working with at the time was a custom-designed time clock system, which actually logged hours, and tracked who was a "student" and who was an "advisor" in the database it used. In the event that any students were still signed in when the last advisor signed out for the day, those students would be penalized by only recieving one hour for the day. I didn't really track it on a per-meeting basis, but just time in and time out. I even went so far as to put it in a multiple-monitor system, allowing for the second display to continuously switch between the current time, and a list of everyone who was signed in at that time. Something that I was working on at the time I left that team was the option to have a form of client/server system, where in addition to the main display in the shop area, you could use any other computer in our work space to log in and out.

Unfortunately, I dont think I have the code for this laying around any more, but if anyone is interested in seeing it(in any form) making a reappearance, I would be more than happy to see what I could put together. Let me know...

[sanddrag]

This is horrible. It makes me so sad.

Please don't log time and attendance. All it does is lead to hard feelings in the end. If you don't know your members well enough to see who is devoted and who isn't, you have more problems than just attendance. Students shouldn't attend just to get the time anyway.

Let students be in it because they care, not because you do.
Mandatory time tracking leads to the downfall of a team.

[seanwitte]

Originally posted by sanddrag
This is horrible. It makes me so sad.

Please don't log time and attendance. All it does is lead to hard feelings in the end. If you don't know your members well enough to see who is devoted and who isn't, you have more problems than just attendance. Students shouldn't attend just to get the time anyway.

Let students be in it because they care, not because you do.
Mandatory time tracking leads to the downfall of a team.

It could also be said that the people who are really into it will be there anyways, so if you log the time it wouldn't matter one way or the other. At least you would have a set of metrics to plan your season by, like saying it took 50 student-hours to build a gearbox. Just because you keep track of the time doesn't mean its tied to anything.

[Nathan Pell]

The only reason I want to track time/attendance is when we travel and have to reduce the size we can have something to measure with.

Also, a lot of students in our school have to do so many volunteer hours to graduate, and they need a record of how many hours they put in.

Nathan

[AJ Quick]

I don't see why you would need to track the time they are there, but taking attendance is good if you have a travel team and want to see who is deticated.

But.. this now makes me want to make a Access database of my own.

[Chris Fultz]

We went really high tech this year and had a piece of paper taped to the door. Students signed in and out and were on their honor.

We use the information for travel - students need a certain percentage of attendance to travel with the team. They also need to attend to stay on the team. Not having a record has caused problems in the past because some students may be at the meeting but not seen by the key adviser because they are working in another area (video lab, animation computers, etc.) or students can claim attendance and with no records of it it is difficult to dispute.

We also use the data for rewards. At our end of season we have a team banquet and have several awards. We award top attendance levels.

Even though attendance does not mean participation, lack of attendance definitely means non-participation.

I like the database idea, but mainly for the experience it would give students to create it - cause that's what this program is all about.

[Brant Bowen]

Originally posted by Chris Fultz
We went really high tech this year and had a piece of paper taped to the door. Students signed in and out and were on their honor.

Hey the paper on the door worked.

We used a point scale based on 3 hours of attendance. I think that 3 hours Mon-Sat, but with 3 hours Saturdays or Sundays for bonus. So basically 1 point = 3 hours and the points were just put in a nomral excel spreadsheet and percentages were figured. You had to get 80% of required time to travel with the team.

Simple, but sometimes simple is all you need to get the job done.

[Ashley Weed]

Originally posted by Brant Bowen
Hey the paper on the door worked.

We used a point scale based on 3 hours of attendance. I think that 3 hours Mon-Sat, but with 3 hours Saturdays or Sundays for bonus. So basically 1 point = 3 hours and the points were just put in a nomral excel spreadsheet and percentages were figured. You had to get 80% of required time to travel with the team.

Simple, but sometimes simple is all you need to get the job done.

on a side note.. a similar system would work well. We were able to get students to sign in and out, and the advisors still knew who was actually dedicated and who wasn't just by the tasks you did. in 02 we attempted time cards, where - for every hour clocked in, you had to put a note down as to the tasks you were doing, and the advisor you were working with at the time.

... biggest issue with any time tracker - be sure to have someone dedicated to keeping track of the work put in, and an enforcer who actually implements the 80% to travel rule.