Not urgent - just learning - database design tips

[thecheyenne]

Hi and good evening / good morning (depending on your location on this planet)
Despite my limited - o.k, non-existing - knowledge of vba, I'd like to design a database to help with the admin of school outings. I pretty much know my way around MS access, but may well need vba for this thing here, so if you have any hints and tips for me, I'd be most grateful.

The outline of the day-out is as follows:

There is a pool of activities (currently 9 different ones) from which students select one activity for the morning session and one activity for the afternoon session.

All 9 activities take place in the morning AND in the afternoon.

However, one of these 9 activities is an all-day-session, in other words if you choose this activity for the morning, you must choose it for the afternoon also.

Some activities can be held in groups of 20, others can only be run in groups of 10 (Abseiling, for example, for safety reasons)

Each student makes his/her choice of activity on a paper form.Each student is allowed a 1st choice and a 2nd choice for the morning session and then a 1st choice and a 2nd choice for the afternoon session, unless, of course, they choose the all-day-activity.

I then get these paper forms and have to come up with 9 'morning-activity-lists, showing each individual activity and its students as well as 9 'afternoon-activity-lists', again showing each of the 9 activities and its respective students as well as 1 'all-day-list' showing which students have chosen the 'all-day-activity'. Whether a student gets his 1st choice or not is determined by a simple 'first come, first served' system.

The paper forms, which students submit and on which they have made their choices are, of course, student-centred, each student submits his/her form with only his/her choices.

When recording student choices on my access-form, I'd like to be able to see how many students are already doing a certain activity and how many more students are allowed to join this activity before the group is full. Once the group is full, students are put into another activity, where there is still room. If possible, I'd like to avoid having to first record all students' choices and afterwards find out that there are too many students in one group and then having to find space for them in groups where there is still room.

The question is therefore: how do I best record student-centered information in an activity-centered way???

So far, I have:
tbl_students (studentID, Name, SName etc)
tbl_activities (activityID; activiyName, groupMax)
tbl_session (session_ID, SessionName, activityID)

To hold the activity choices of each student, I'm thinking of a third table, something like tblStudent_Acti vities with the link-fields Student_Id and Session_ID so I can then base a form on this third table, and match up each student with his/her 2 activities.

It's with the design of this form that I'm struggling a little.
Any hints, tips or ideas?
Thanks for taking the time to read this.

[ChipR]

tbl_activities would need a field for whether the activity is all_day or not.

I envision a form for one student on one day with 4 drop-downs: morning 1st choice, morning 2nd choice, afternoon 1st choice, afternoon 2nd choice. These comboboxes allow a choice of any activity. Then while you are entering in the choices, you can see how many students are assigned to that activity, and go ahead and assign this one at the same time if you want to.

So let's make this a record.

tbl_choices (student_id, outing_date,
morning_choice1 , morning_choice1 _status,
morning_choice2 , morning_choice2 _status,
afternoon_choic e1, afternoon_choic e1_status,
afternoon_choic e2, afternoon_choic e2_status)

Possibly with some shorter names :)

So all the choices are activity numbers. All the status are either: confirmed, rejected, or pending
Then you can see how many students are assigned to an activity by counting the records for that session, that activity, and confirmed. You can easily have a control there on the form that gets that total and update it when an activity is selected from the dropdown.

You could have a button or another dropdown next to each choice to set it's status by confirming or rejecting it or leaving it pending, and when that happens update the control showing the total again. This way you can assign the students to the activities as you enter in their choices.

I think the table of choices will allow you to get lists of whom is assigned to which activities and vice versa, by querying which choices are confirmed, while still keeping records of other choices, and allowing you to enter the information all at once. But this is my first quick take on the situation, so remember it's just random ideas right now. You may have a totally different preference for how you would enter the information, and that might necessitate another design altogether.

[thecheyenne]

Hi ChipR and thanks for the interest and tips.
In the end I came up with something similar, although I'm not sure yet whether this is the best it can be.

As for the activity-sessions, I've split it right down into tbl.sessions (am/pm/all-day)
and then a junction-table tbl.activity-sessions (activitysessio n.Id; activity.Id; session.Id). The idea is to then combine the activitysession .id with a student.id.

my final table, I called it tbl.www (who/what/when) therefore has the fields www.id; activitysession .Id, student.id Here, what I really would like to achieve is set a maximum, so that no student can actually have more than 4 activities assigned to him/her.
Any Ideas, anybody?

tbl.www also contains a field called priority, which allows a choice of numeric value, either 1 or 2. A 1 means it's first choice and will be awarded wherever possible up to the number of students allowed for each activity. A separate query will count the occurrence of '1' and display the total in a subform, more of that later.

Data is entered via a Form with subforms. The mainform is frm.www. The name is misleading, I know, since the mainform only serves to select a studetn and has nothing to do with who/when/what. I will probably change the name in the final version.

When entering data, speed is of the essence, so to speak, and finding a student's record whilst holding the paper form in your hand should be quick and effortless. frm.www therfore has two combo-boxes, and the second combobox displays only records relevant to the choice made in the first combobox. I am happy with that.

What I'm not yet happy with are the sub-forms.

The subform 'sub-sessions' relates to tbl.www and gives me a who_what-when-id, an activity_sessio n-Id, a student-Id and a priority-Id.
The Student-ID is the link-field between main-form and subform

Here, what I really would like to achieve is set a maximum, so that no student can actually have more than 4 activities assigned to him/her.
Any Ideas, anybody?

Counting the number of participants in any given activity session;
At present, the only way I've managed to do this, and it's not perfect, I know, is by having a query count the number of priority-fields containing the value 1.
The result of this query is displayed in sub_form 'session-overview' and this subform sits within the subform 'sub-sessions' which means that subform sub-sessions can only be displayed in form-view and you therefore have to use navigators to click through the 4 choices for every student. I'd much prefer to see the 4 activity choices in either continuous-form-view or data-sheet view but neither will allow a sub-form to be displayed.

Any ideas anybody?
An alternative would, of course, be to call up the result of of
query_session-number-overview via a command button each time you want to assign a student to an activity but that is also less than perfect. Perfect would be to have the result of that query sat in the main-form but I've not (yet) been able to establish a link between the two forms.

Anyway, thanks for reading and feel free to advise.