How to include multiple age range categories in table design

[lzuke]

Newbie here.
We are converting a task previously done on spreadsheets, so it's not terribly complex, but I'm stuck.

The 8 different offices in our govt organization all do community events. For each event, the office must report the following:
--Office where event took place


--Date of event
--Name of the Event
--Room event took place in
--Type of event (children's event, adult event or public use)
-- AND for each of four AGE RANGES, the number of people who attended the event (Age Ranges are Age0-5, Age6-11, Age12-18, Adults) Our staff just does a head count at the event and enters the four numbers. This is the part that is stumping me!

In my first database attempt I did not try to make a separate table of age ranges, but just listed each Age Range as if it was a completely unique field in the Events Table. It was easy to make my data entry form this way because I WANT all four age ranges to SHOW on the form so staff can just enter the quantities in the proper field. I can make my reports just fine from this set up SO FAR, but I feel that it's not proper database structure to list these age categories separately. But I don't know what table structure to use for this.

Hope I provided complete information.

[Rod]

Interesting question! You may get many different opinions.

Strictly, the number of people in an age-range group is a derived value. Similarly the age of a person is derived from that person's birth date. Just to complicate matters further it may occur that the authorities change their age-range groupings in the future. Age ranges are a contrived concept.

Thus the 'correct' (purist) design would be to have a table for individuals with provision for recording birth date, a table for events, a table relating individuals to events, possibly a table for event types, and possibly a table (structure/hierarchy) for locations (offices and rooms). The definition of age ranges and the count of individuals in each range would be achieved through a query.

Obviously from what you describe this is completely 'over the top.' It seems you do not/cannot collect information about each individual. Moreover you do not seem to be interested in any attributes about the individual apart from age-range (not even gender?). Therefore the individual, attendance and event table structure collapses into one table for events with associated age-range counts.

This diatribe is a long-winded way of saying you were right the first time! I think you will gain nothing by creating a separate age-range table. I would encourage you, however, to think about implementing that office - room table hierarchy if: the number of locations is large; the locations do not vary much over time.

So, stick with having age-range columns in your event table where moderators may enter headcounts.

[lzuke]

Thanks very much Rod. That's encouraging.