Easy Question

[BenjaminDz]

Hey all... just doing a practice exercise for one of my last semester classes to get a better understanding of relationships. If you see the attached database, any idea on why I can't create referential integrity when trying to link-up StudentID with StudentID for all tables?



For example, I would like to be able to click the "+" sign on a Students name and have all the info underneath it that links the tables together... so for example, StudentID "1" will have all the info of course then when you click the "+" sign it will have mother info, father info, fee info, and guardian info.

Is that possible?

[ranman256]

1. I dont see a reason to have a mother AND father table. All people can exist in a single table with M or F relation code.

2. You dont want the tStudent key, StudentID, to exist in tFather,tMother, etc as AUTONUMBER. It MUST be set to LONG INTEGER.
The autonumber in tStudent links to the integer key StudentID in Father, mther,etc. These child tables can have an Autonumber as PERSONID. But not STUDENTID

[BenjaminDz]

1. I dont see a reason to have a mother AND father table. All people can exist in a single table with M or F relation code.

2. You dont want the tStudent key, StudentID, to exist in tFather,tMother, etc as AUTONUMBER. It MUST be set to LONG INTEGER.
The autonumber in tStudent links to the integer key StudentID in Father, mther,etc. These child tables can have an Autonumber as PERSONID. But not STUDENTID

Well this is a first year Access class and the instructor said make as many tables as you need -- everyone's database will be different. We were given a raw Excel spreadsheet to pull data in and I figured because Mother and Father have their own pieces of information that is unique, I should separate that out. What you are saying to do is eliminate phone numbers, emails, etc... and just do a single table with a M or F?

Also, I am a little confused on your second step. So what you are saying is to remove the Autonumber from all other tables but the main Student table right? If so, can I then set relationships as stated above? I would like to have the StudentID hold all information about that specific student... So who their mother and father are, fee's, and Guardian information.

I really appreciate the help guys!

[June7]

Might look at the MS Desktop Student template database for ideas.

[BenjaminDz]

Well this is a first year Access class and the instructor said make as many tables as you need -- everyone's database will be different. We were given a raw Excel spreadsheet to pull data in and I figured because Mother and Father have their own pieces of information that is unique, I should separate that out. What you are saying to do is eliminate phone numbers, emails, etc... and just do a single table with a M or F?

Also, I am a little confused on your second step. So what you are saying is to remove the Autonumber from all other tables but the main Student table right? If so, can I then set relationships as stated above? I would like to have the StudentID hold all information about that specific student... So who their mother and father are, fee's, and Guardian information.

I really appreciate the help guys!

Already did but that helped me none... no relationship tables are created and it's way different than mine.

[June7]

I seldom build relationships but they seem rather obvious in the template. It is just supposed to give you ideas.

I agree with ranman, separate tables for each parent is not optimal. Normalization means should be one table with multiple records as demonstrated by the template. Alternatively, fields in student table for each parent is not normalized but manageable.

[BenjaminDz]

I seldom build relationships but they seem rather obvious in the template. It is just supposed to give you ideas.

I agree with ranman, separate tables for each parent is not optimal. Normalization means should be one table with multiple records as demonstrated by the template. Alternatively, fields in student table for each parent is not normalized but manageable.

Ok so then how would you build the Mother/Father table? Just combine the information in one table and call it "tblParent"? If so, I still need to figure out the relationship piece as that was one of the requirements. I just want to get a better understanding of the basics so I can apply it in the future.

[June7]

tblParents with field to save StudentID.

The template allows each parent record to be associated with multiple students. Do you want that as well?

[BenjaminDz]

tblParents with field to save StudentID.

The template allows each parent record to be associated with multiple students. Do you want that as well?

What do you mean field to save StudentID? Given that multiple students have the same parents, yes, I would like that if possible.

So ultimately, I feel like I am asking something simple but still can't derive to a conclusion. I want to be able to have the "+" sign under main Student table where it drops down all info about that student... so parents info, fees, guardian, etc... is that possible at this point?

[June7]

Do you understand the purpose and use of primary and foreign keys? StudentID is primary key in tblStudents. A corresponding number field is needed in tblParents to relate student and parent(guardian) records.

The +/- expand/contract feature would require establishing relationships in Relationship builder.

Ideally, users don't interact directly with tables and queries, only forms and reports. A form in Datasheet view can have the +/- expand/contract feature once relationships are established. But I have never set up a db with this.

Apparently the template does have the table structure you really want. Just needs relationships set up.

[BenjaminDz]

How is this now for the table?

[June7]

I was assuming each parent would be a separate record. The StudentID field would not be autonumber type, just number (integer) type to save the StudentID from tblStudents. What you have now is an autonumber ID for the tblParent record and both parents are in same record. This means the ParentID could instead be saved as foreign key in tblStudents. This will allow multiple students for each parent. If each parent has their own record, save ParentID into tblStudents in two fields (Parent1 and Parent2). However, the template model is more normalized structure.

Why separate tables for Guardian and Parents?

[BenjaminDz]

OK so I made ParentID and hide it from the view and now I have exactly what I wanted this entire time by adding in the ParentID... What else do I need to do at this point? I'd like to keep Guardian Separate if at all possible as the assignment required 4 tables.

The only thing left is what you mentioned above... I still can't figure out how to list 1 parent and then two kids underneath instead I still have duplicate parents.

[June7]

As stated in my last post, because both parents are in one record, can reverse the PK/FK association. Fix Relationships. Save ParentID into tblStudents. Eliminate the duplicate parent records. Open tblParents and should list students with the +.