design question - subforms or separate forms?

[mairead]

Apologies in advance for the very basic knowledge required. I want to find the best way to design the following:
I'm creating a database for use in a faculty office, to hold info on students in 4 categories- deferral, extension, subject change, year out. So I have a student table, a categories table and then I'm thinking 4 tables for holding details of the relevant category (deferral etc.). Is that the way to go?

Then I have a form with the student details and a look up table for the 4 categories. If a student is a deferral for example, should I display that info in a subform that becomes accessible when the 'deferral' category is selected. And so on for the other three categories. Or should I create a separate form for the four options?

Is there a better way to design this? I'll need to be able to create reports and queries for example, all students who chose to defer in 2004.

 

[FoFa]

Your basic question, before your form question is how do I need to design my tables. Form logic follows table design. Student table is good, categories table, is that just a lookup table holding category information, sounds like it and that is what it should be from what you said. Now the category status of each student. Are the data elements much different between the four statuses? In other words are the fields used for deferall all different from year out, or are there just a couple differences, or no differences? Can more than one category status be held by a student at any given time? Do you need to keep history on past category statuses held by a student?

 

[mairead]

Thanks for the reply,
Student table holds basic details, id no (primary key), name,address etc.
Category table is 1. Deferral 2. Year Out 3. Extension 4. Subject Change

The data elements in these 4 cats. would have little in common.

A student could be in more than one cat. during the course of an academic career i.e 1st year - change subjects, 3rd year take year out. This would be the exception rather than the rule, but no reason it couldn't happen.

I would need to keep a history.

Any hints on how to progress?

 

[FoFa]

This is where it gets tricky now since the four categories have little in common on a field level. You design one table that holds all the different fields knowing you are not going to use (as and example) 3/4 of the fields for each row. Probably not the best design, but easy. You could have four tables one for each category since the data elements are unique, maybe a better design, but a little harder to query. OR a table to track category with a datetime, active/inactive category flag (as an example), any like fields between the four categories, student foreign key and a primary key that can be tied to from one of the four category tables. But with the four table design on categories, you would be better off with separate forms rather than subforms I think. Lets see what others have to say also, maybe we can Pat to add something to this.

 

[mairead]

ok, there won't be a huge amount of fields on any of the 4 tables so they could be amalgamated at that level. so when I create my form where do I go? i have all my student info at the top of the form (from my studentinfo table), then a selection : deferral, year out etc. Would a button with a macro that opened the relevant form be appropriate? I need to keep this as simple as possible! will check in again tomorrow. Thanks