am I on the right track? - attendance database

[kular]

Hi all;

This is probably so basic that I am embarrassed to ask, however I would like to get off on the right foot. So here goes my first post to this forum.

I would like to build a database that tracks attendance at events by date.

Components would be the first and last name of the person, the category of event, the name of the specific event and finally the date that the person attended. A person could potentially attend more than one event on any given day.

So here is what I am thinking:

A date table with a one to many relationship with the member.
A members table with a one to many relationship with a category of event
A category table with a one to many relationship with the activity table
Finally an activity table


For instance, an example of a category might be “Sports” and specific activities could be “tennis”, “golf”, “hiking”, etc. There could be a dozen categories and 100 different activities.


Required reports would include:

The number of people who attended each event
A list of people who attended each event
The number of people who attended each category of events by week or month
A list of people who attended each category of events by week or month
The number of events that a member attended by week or month
The specific events that a member attended and the date associated with each

How to manage the date is the dilemma that I am struggling with, or am I getting too fancy here?

Any advice would be greatly appreciated or even a confirmation that I am on the right track.


Thanks in advance.


kular

 

[wazz]

So here is what I am thinking:

A date table with a one to many relationship with the member.
A members table with a one to many relationship with a category of event
A category table with a one to many relationship with the activity table
Finally an activity table

i think you're missing one important ingredient: an event table. and if an event is unique, which i guess it would be, your date could go in there, and every activity (and everything else) would fall under that "roof".

and, are you familiar with 'many to many' relationships and junction tables? be sure to search for info about this very important concept.

 

[kular]

Hi wazz;

When I re-read my post, I realize that I added some confusion there. I am thinking of an “activity” as an event. Think of them as the same thing. I wasn’t clear on that at all.

I am not familiar with a many-to-many relationship, however will do my homework on it and how it might fit into this picture.

It seemed to me that I would need a separate table for each event/activity as a place to park the date of that event, hence my contemplation of having a date table and associating the member, category, and event back to that date.

It seems a bit odd to me, however that seemed to be a better than having a unique table for each of 100 events.

Thanks for you input.

kular

 

[The_Doc_Man]

From the description of your intentions and design, I infer that you are not fully aware of or comfortable with "database normalization" - an incredibly necessary subject to study when trying to build databases. Two places to start: Access Help on "normalization" and follow that by Wikipedia.ORG on "database normalization." You can also use your favorite web search engine to find articles on "database normalization." Do not search for "normalization" by itself, since that word is used in international politics, matrix math, and some obscure physical-science references as well.

When you do this more general search, ignore articles from places you don't recognize. I've seen some really GREAT reference articles on-line from various .EDU domain sites - colleges and universities. Do some reading until it all starts to sound familiar. (Because pretty soon it WILL start to all sound the same... ).

The general idea of "normalization" as a database design action is that you are trying to abstract the elements of what you are tracking with a database. In some senses a database can be thought of as a model of some real-world process. That process has actors (potential elements), actions, and objects (more potential elements). The elements go into tables. The actions are expressed through forms, reports, queries, and macros - plus code expressly or implicitly run by any of the latter.

The very first part of a design is to "purify" the elements of your design. That is, isolate their essences, separate them according to their roles in the model. When you can do that, you are doing a true normalization. This is a trickier step than it might sound, because normalization takes some odd turns now and then.

Having said that, here is the thing to consider. It is possible to over-purify. An activity or event is a trackable element. If you put a date, activity class-code, and maybe a couple of foreign keys (FK) leading back to other relevent tables, maybe that is all you need for any event tracking whatsoever. (Depends on your goals, of course.)

Different question: The idea of a many-to-many relationship exists in the real world. Access has this pesky little restriction that it doesn't do many/many in one step. So when searching this forum for the topic, look up "JUNCTION TABLES" as the keyword that will lead you to that concept.

When you shoot for any Access design, you do yourself the greatest favor by making the design reach what is called 3rd-normal form. (You'll understand that when you read up on normalization.) Less than that and you will likely have trouble. More that that is OK but not always necessary. From your description, I'm betting that 3rd normal will be all you need. But hey, I've been known to be wrong before.

 

[kular]

Thanks to The Doc Man.

……for taking the time to provide me with your wisdom.

You are right; it does all begin to sound the same.

And I took a sweep through the concept of junction tables too.

Unless I am missing something here, I think that the design would be considered to be normalized.

There are only four pieces of data required here.

The member’s name, and that could all be in one field.
The category of event – e;g. sports, culture, etc.
The name of the specific event – e.g. cycling, golf, swimming, etc. for the sports category.
The date that the person attended the event. This is the challenging part for me, as a member often will attend more than one event on the same day and attend the same event as often as on a daily basis.

That is all there is to it.

There are about 200 members, a dozen different categories, and about 100 events. Some events attract over a hundred members, and some only a handful.

This looks a lot like a simple spreadsheet application until the date enters the picture when it needs a third dimension, hence my thoughts that an MS Access solution might solve it. Currently attendance is being recorded on a spreadsheet that consists of a list of member’s names in Column A, and the events listed across the spreadsheet in Row 2. Row 1 has a number of merged cells that serve to bundle the events into categories.

The user currently is recording attendance by adding an “X” in the cell when a member is present. The results are usually tallied on a monthly basis, and as previously mentioned, a member might attend the same event every day, or every week in some cases, and what I am looking for is a method to capture each date that a member was present. With this spreadsheet we only know that a person attended at least once, not how often or on what date(s).

A member would never attend a single event twice on the same day; however over the period of a month they could very well attend the same event many times and currently we are capturing that information on paper and would like to come up with a method that would enable us to generate much more valuable reports.

My ultimate goal is to develop a form that will permit the user to quickly select the member’s name, select the category form a combo box which would then cascade to another combo box revealing the specific events associated with the selection in the first combo box, and finally select the date from a calendar control.

I mentioned the reports in my earlier posting, and I am hoping that if I get off on the right foot here, that I will be able to generate them without encountering too much grief.

So with all of this explanation, I am thinking that I might tackle this with a table containing the dates (one date might relate to many members), a second table containing the members (one member might relate to several categories), a third table containing the categories (each of which would have several events) and finally the events table itself,

Does this approach have some flaws?

I haven’t been able to figure out how a many-to-many relationship would help here, so I must be missing something. No, make that a lot!

Man, I am long winded!!!!!

kular