Proper Database Design??

[jondavidf]

Hello all,

I have looked all over Google, YouTube, Office Templates, and here for a database similar to what I am trying to create, but I can't find one that I can modify for my needs. It seems relatively simple what I want to create, but I just haven't found it yet. Here is what I'm trying to do:

I am attempting to create a database that tracks attendance at activities. The things I want to know are:

1 - Who is at the activity?
2 - What activity are they part of?
3 - What was their participation level in the activity?
4 - What is the date of the activity?

There can be multiple attendees at multiple activities throughout each day. Here is what I've come up with so far:

1 - A resident table that has basic information about the resident, including ResidentID(PK) and Lname, Fname, etc.
2 - An activity table that includes only ActivityID(PK) and ActivityName. Activities do change from time to time, which is why I don't want each activity being part of the resident table.
3 - A history table that has ResidentID, ActivityID, ActivityDate, and ParticipationLevel(of which I have created a value list of Active, Semi-Active, etc.). I have NOT designated a primary key for this table.

Both tables 1 and 2 have a one to many relationship with table 3 with ResidentID and ActivityID, respectively. This is a different design than any others I have done, since there is nothing that directly ties the resident table and the activity table. The basic info I want to capture is the ResidentID, ActivityID, ParticipationLevel, and ActivityDate (which is all in the history table).

Does this setup seem feasible?

If it does seem feasible, how would I use queries and forms to make data entry simple. Preferable scenario is to have a form with a date on the top, linked to the History Table (which I believe I know how to do) and in the middle a display similar to that of a spreadsheet where I can have the rows display the names, the columns display the activities, and the inside fields consisting of combo boxes where I could choose the ParticipationLevel.

How would I make this possible? I believe it would use update and append queries tied to command buttons on the form, but I can work that out once I get the form figured out and if the whole database even makes sense the way I have it configured.

Attached below are 1) Example of database I created so far. There will obviously be many more residents and many more activities once I have the proper design of the database. 2) An excel spreadsheet that would be similar to how I would like the main part of my form to look.

TestDB1.accdb
Daily Attendance Roster.zip



Your help is very much appreciated. This would save hours of manual tracking that is done each week for these activities. Thanks.

[Rod]

Looks OK to me - I invite others to comment - except:

1. Avoid spaces in attribute names. LastName is so much easier to code than [Last Name] (requires brackets each and every time you use it). Avoid spaces also for table names.
2. I encourage you to use standard Reddick tags for table names, etc. so History Table becomes tblHistory. Don't use tags for table attributes.
3. I would include a meaningless primary key in tblHistory, say HistoryID (Autonumber, PK).

I'm surprised you did not find a standard db template for this.

The Attendance Report will be a challenge.

[jondavidf]

Thanks for your comments. I'll make the structure changes you recommend. I, too, am surprised there wasn't a similar database out there. I did modify one that was just based off attendance, but I had to have each activity in the resident table and the history table. The problem I had was that if anyone needed to do something as simple as changing one activity name then two tables and a form had to be modified, which is beyond the average users capability.

[orange]

I have 2003 so can not open your accdb.
Here's a link you should investigate,
http://www.databaseanswers.org/data_...ters/index.htm

[jondavidf]

I'll look at converting the database to an mdb file later today. The database relationships in the link you provided is a little more complex but otherwise similar to what I have. If the database design makes since how could I make it simple, likely through a form, to enter data? I don't want to have to rely on knowledge of ID numbers to enter the data.

[orange]

I was pointing to a model that involved Roster that might assist your search. You do not have to use the model - even pieces of it, or concepts it deals with -- may be suitable to your needs.
Simpler interfaces --Correct, the underlying database structure and the use of unique numbers to identify table relationships is for integrity etc.
You can make forms and controls to facilitate user interaction that do not display or require the user to know the unique numbers.
Investigate GUI design, useful Forms and similar terms.

[jondavidf]

I think I know what minor changes to make to the database to make it a little more structurally sound. Now comes the difficult part for me - figuring out how to create forms/queries to insert/update the information. Any ideas for form creation - ideally I would have a spreadsheet type setup with the date in the form header, names as row headings, activity types as column headings, and participation level as fillable fields. Alternately I could have a list or combo box to select an activity, then have the names in a column and participation level in another column. I'm not sure exactly how to do that though. Any idea how to create the form or where to point me to find out?

[orange]

You make references to spreadsheet -- but I must advise you that Access (database) is quite different than Excel(spreadsheet).

Here is a somewhat dated link that deals with Forms. It is from a set of tutorials.
http://sfubusiness.ca//areas/mis/tut...ons/forms1.pdf

You can google for MS Access Forms tutorials on youtube as well.

NOTE: Getting your table structures correct is the key to successful database.

[jondavidf]

I actually do have some experience drawing data from excel to access via a spreadsheet, but that is not what I intend for this db. I was just thinking of a spreadsheet type of appearance to enter data via a form.

Any ideas how to do this with the given variables above? I will check out the links recommended above, also, I just haven't yet had time to do that.

Thanks again for the help.

[Rod]

Jon,

What daily input volumes are you expecting? If overwhelming then Excel input may be the way to go (but quite complex). My comment about your preferred report being a 'challenge' was based on the fact I thought Access' Pivot feature was not really appropriate and that you would need to poke your report data into Excel.

[jondavidf]

I'm looking at input volumes of approximately 5 activities a day with about 15 attendees per activity. The form doesn't have to have a spreadsheet view, that's just what they are currently using so everyone would already be familiar with the layout. I'm open to whatever would work best. What do you think the best option would be? Again, I'm looking to input participation levels for various attendees at various events throughout each day.

[Rod]

On those volumes I would forget Excel and confine the data input to Access. I envisage a cycle whereby the user selects an (or enters a new) activity and then associates 15 attendees to the activity (with perhaps the opportunity of defining a new attendee). Five cycles? Twenty minutes at the most. No don't get me wrong, that's not processing time, it's elapsed staff time from log-on to log-off - maybe there's a cup of coffee in there too.

Who will be using this? You? Someone else? More than one person simultaneously?

Let's see the latest db and I'm prepared to help you with the design.

What do you prefer: tabbed windows or overlapping windows?

[jondavidf]

I will upload the modified db shortly. The activities will be attended by a varying selection from 52 (more or less, depending on resident population) personnel. It would be nice have all their names listed on a form and not have to scroll through different pages to get to the user we want. It will be primarily one person, my wife, using the db but there may be others that need to access it occasionally. There shouldn't be a need to have it accessed by more than one person at a time, but that would be neat to have it on a share drive and accessable to all. For now it will just be installed locally on one PC without a need for remote access. I'm guessing I would prefer overlapping windows. I just can't quite picture that in my head, though.

[Rod]

You've just made my day, overlapping windows it will be! (I have a personal hate of the technicoloured, trendy, filing-cabinet look of tabbed windows.)

The 52 (or so) potential attendees will be displayed in a scrolling list (not in pages) sorted in the sequence you find most convenient. Provided that the window is sufficiently large, about 50% will be visible at any one time; I don't think you'll find that too clumsy. As for the report, I think it will be poked into Excel because of the cellular cross-tabbed format you want.

I like to try, when answering these posts, to teach as well as supply solutions such that my correspondents become self supporting. In your case however we'll be all year swapping posts if I ask you to do everything. So I shall write large parts of the solution (well commented I hope) and explain in the posts what I have done. They will act as a model for the future.

I shall provide only the single station solution; a split implementation (server-client) I shall leave to someone else for the future. If it is necessary to have security/privileges between the various users of the single station solution, then we can discuss that later.

By the way, I hate macros as well, so my solution will include VBA code. I like to be in as much control as possible and VBA provides that. However I detect a trend that Microsoft wants us to move to macros so they can abandon what they percieve as an out-of-date programming language.

I await your db design - if necessary, make a copy, empty the tables of data (in case it's sensitive) and post the empty copy.

P.S. Is the variable 'level' that you mention an attribute of the activity itself or an attribute of the attendee's role at that activity?

[jondavidf]

I have attached the database with changes. I basically just renamed the tables and took the spaces out of the fields. I also added a HistoryID field to the tblHistory.