Student Database - fields vary for students.

[ArmyLT]

Hello all - first post!

I'll try to be clear on what I'm shooting for here.

I'm tracking information about students in a course. My Commander is always trying to find information about his 600 students across 6-7 concurrent classes. The Cadre that run each class use spreadsheets to track all information about the students - and they all do it differently. Spreadsheet-1 is demographics (about 75 columns of information), Spreadsheet-2 is academics, Spreadsheet-3 is for weapons qual, Spreadsheet-4 is... well you get the idea. Again - the sheets vary from Cadre to Cadre in setup and use. Finding quality information is - painful.

I've not done much database since DOS Paradox - so I'm a little rusty. I won't be here much longer, so I'm looking to make a solid foundation with my design.

I'm tackling Demographics right now... the spreadsheet has columns for everything (car1, car2, Barracks1 (Y/N), Barracks2(Y/N), Barracks3(Y/N), Off-Post(Y/N), BarracksRoom#, BarracksPhone#, Off-PostAddress, Wife, Kid1, Kid2, Kid3, Kid4). Again you get the idea...

Depending on the LT, he'll have a different set of info we'll track - if he's Guard, Reserve, or Active; living off-post or on; Married or not; kids or not, etc. And I don't think one big table tracking every possible combo is the right way to go - but I'm not sure.

On the form, I'd like to have a radio button for "On Post" "Off Post." Depending on that status, it'll present fields and populate the correct table(s).

In the case of on or off post (which I then can hopefully apply to other situations). Would I have tblStudent, tblBarracks (with only the 3 on-post facilities), and then a tblStudentBarracks. (Autonum, StudentID, BarracksID) to connect them? But if they have a off post address just have a 1-1 tblStudentOffPost (Autonum, StudentID, Street, City, State, ZIP)?



That make any sense?

Thanks much,

EngLT

[RuralGuy]

Boy oh boy oh boy. Spreadsheets are basically a flat file and Access is a true RDBMS. It would really help you if you brush up on Normalization. Plunking data into a table from a spreadsheet is easy. Getting it back out in some meaningful way later can be a nightmare if it is not Normalized.

[ArmyLT]

I've got a pretty good grasp on normalization, but in my limited experience, it's always cut and dried - and relationships flow pretty easily. One Customer has many orders - and each order can have many line items.

But I'm struggling a bit with the concept of what I'm trying to do. One student lives on-post and we'll track his on-post info (barracks, barracks room, barracks extension). But his classmate may live off-post, and we won't track barracks room and phone, but address and city and zip.

I think what I'm shooting for is two one-to-one relationships ("Or'd" together if you will). tblStudent (1)<=>(1) tblBarracksInfo OR tblStudent (1)<=>(1) tblOffPostHousingInfo. One student record will match up with EITHER one of the HousingInfo records.

I'll keep reading and playing with it...

... and Lord how I miss Colorado....

EngLT

[RuralGuy]

The hair on the back of my nect stands up when I see repeating fields like Kid1, Kid2, etc. Generally speaking you rarely have 1:1 relationships. It usually means the fields should be in the same table; that is unless you have split the data intentionally to limit the fields in any one table.

[RuralGuy]

Have you looked at any of the templates that ship with Access or those on the MS Office site? There may be something close to what you want that can then be modified easier than starting from scratch.

[ArmyLT]

Here's what I did....

I have a table with basic information:
StudentID, FName, LName, SSN, Rank, etc.

Then several tables that may, or may not, or even be mutually exclusive.

tblOnPostHousing
StudentID, fldBarracksArea, fldBarracksBlding, fldBarracksRoom, fldBarracksPhone

tblOffPostHousing
StudentID, fldStreet, fldCity, fldState, fldZIP

tblPriorServiceInfo
StudentID, fldPriorBranch, fldPriorMOS, fldPriorRank, fldPriorDate

These allow for 1 to 1 relationships between the tables. That is one student record in the Student table, can only be linked to one record in the other tables.

On the data entry form, if you select "On Post Housing" only the fields for that respective table show up.

Clear as mud?

I'm no expert by any means, so if someone wants to kick me in the shins for heading down the wrong path - won't bother me a bit. I've moved on to wrestling with the idea of bound vs unbound forms....

- Eng LT

[RuralGuy]

Why does the spreadsheet need the Barracks1 (Y/N), Barracks2(Y/N), Barracks3(Y/N) fields? And please have a family table with the StudentID as a ForeignKey in each appropriate record.