I am trying to learn Access and have run into a couple of things that I may not have done correctly. I have created a Student table with the student first and last name as the PK. This file also contains Parent 1 First and last name and Parent 2 First and last name (parent 2 info will only be entered if parents are divorced otherwise it be blank). The parent table has Parent First and last name as the PK to that file.
I have created 2 relationships between the student and parent files. 1st is the parent 1 F&L name to the parent table F&L name and another relationship of parent 2 F&L name to parent table F&L name.

When I create my sub-form, it has parent info as the main part of the form with the children listed below. The problem is that it will only list children for the Parent 1 information. The strange thing is that Parent 2's child record has the 2nd parent information in the 1st parent fields on the sub form. Can Access handle this type of relationship?

I did not get what I expected from my query either. I ended up creating 2 queries by establishing the relationship at the query level for each. The problem with this is that they still have to be linked back together somehow if you want to print labels for instance.

Any help will be greatly appreceiated.

thanks Cindy

Hi Cindy -

It sounds like you really just need ONE parent table, with a FK to the student table. That way you can have as many parents as you need per student. Then when you called up a student OR parent you would have access to the associated parent or student. Would that work for you?

Jennifer

Jennifer,

Thanks for your reply.

Technically I do have one Parent file.

I have a child file with the following:

child 1st Name (key)
Child last name (key)
Par 1 1st Name (required)
Par 1 last name (required)
Par 2 1st name (only if parents divorced)
Par 2 last name (dito)
+ other data

The Parent file is made up of the following:

Parent 1st Name (key)
Parent Last Name (key)
+ other data

I set up my relationships as follows:

Child par 1 lst name to Par 1st name
and child par1 last name to parent last name.

the second relationship is:

Child par2 1st name to parent 1st name and child par2 last name to parent last name.

the problem is that on a query or the subform it doesn't seem to like the second relationship.

I need the sub form to display all the parents children no matter which parent I am looking at. Right now the 2nd parent has lost their children!! What is odd about the second parent on the Subform is that it is defaulting in to the 1st parent field on the child portion of the subform (where the next new record would be entered).

I program in RPG on the AS400 so am trying to learn something new. I am probably creating my own problem here but don't know how to resolve it, yet...

I hope the above makes sence.

Thanks for your help in advance.

Cindy

PS. Jennifer I see you live in Eugene. I grew up near Grants Pass. Small world!!

Without getting into the philosophical reasons to keep all parents involved *grin*, consider revamping your Parents table:
ParentID (Autonumber, most likely)
ParentFirstName
ParentLastName
...
ChildID (Long Integer, matching the PK of the Children table)
ParentRole (for type, see below)
This field should be a combo box, possibly value list (text) or its own lookup table (use the Lookup Wizard). It could have values like:
1) Custodial Parent
2) Custodial Guardian
3) Resident Guardian/Parent, Non-custodial
4) Non-custodial, non-resident parent.

Anyway, you know the types better than I. Point is, you can now assign a child as many parents as you like. Then in your form pull up only those parents with [ParentRole] = 1 or 2, for example.

Does that help?
David R

[This message has been edited by David R (edited 03-12-2002).]

Hi Cindy -

Everytime we drive through Grants Pass we stop for ice cream at some place my husband just loves and he calls the town Grass Pants. Silly : )

I think what I was getting at with your tables is that if you have an ID (autonumber or other) for your Parent in the Parent table (especially since two people can easily have the same first and last name like Joe Smith)then you are guaranteed a unique key. Use this unique key as a FK in your Child table with a field named ParentID. Then your relationship is simple. ParentTable.ParentID to ChildTable.ParentID
This should make your queries very happy, and on data entry you can easily require that one parent is entered and have a place to enter another optional parent. Does this make sense?

Jennifer

Not only may students have more than one parent but parents may have more than one student. Therefore you have a many-to-many relationship which requires three tables. However since you should store students and parents in the same table since they are all people (well at least most of them) this particular relationship uses two tables.

tblPerson:
PersonId (autonumber primary key)
FirstName
LastName
...
TypeCode (parent, student, teacher,..)

tblRelationships
StudentId (primary key field 1)
ParentId (primary key field 1)
Relationship (mother, father, step-mother, guardian, ...)

The relationship looks like:

tblPerson tblRelationships tblPerson_1
PersonId ---> StudentId
ParentId <---------- PersonId

[This message has been edited by Pat Hartman (edited 03-12-2002).]

Pat,

Thanks for your response. I am still a little confused...could you explain why what I did doesn't work? How is what I did different than your example.

I have:

ChildTbl ParentTbl ChildTbl_1
Par1LstNm ------> ParLstNm <----- Par2LstNm
Par1FstNm ------> ParFstNm <----- Par2LstNm
(Required) (NotReq)

On my subform I can look at the parent (which is par1 on the child record) information and see the Children listed below
as it should be. IE:
Par1Nm = Jack Jones
Par2Nm = Jane Smith
Child = Dick Jones
Child = Jean Jones

Sub form shows:

Parent Portion:

Jack Jones (yadda yadda)

Child Portion:
Name DOB Par1Nm Par2NM
> Dick Jones Jack Jones Jane Smith
> Jean Jones Jack Jones Jane Smith
* Jack Jones

But on Parent 2 I see

Parent Portion:

Jane Smith (yadda yadda)

Child Portion:
Name DOB Par1Nm Par2NM
* Jane Smith

I understand about making the names UNIQUE but I need to understand why this senario does not work. I also know I am thinking in terms of what I can do in RPG and the AS400 and am trying to look at this a different way!! Kind of hard (headed) after years of the other (how many I won't say).

Thanks for your help.

Cindy

I would disagree that parents and students should necessarily go in the same table since you may be storing very different information on each of them. However beyond that Pat is correct that you have a many:many relationship and will need either a self-referential relationship through an intermediary table, or three tables.

Thanks for the catch Pat.

David R

Problem 1 - using first and last names as a primary key. It is quite possible for two students or two parents to have the same name. Use an autonumber as the primary key to ensure uniqueness and use a non-unique index for last and first name for efficient lookups.

Problem 2 - fields that have numeric qualifiers. This is a prime indicator that you have a repeating group which violates first normal form. Relational databases deal well with three quantities - 0, 1, and many. There is no concept of 2. Therefore you should NEVER see a properly structured table with field names like Par2Last and Par1Last. And don't think that making the names MotherLast and FatherLast resolves the issue. That just makes it worse in these times of same sex marrages and obfuscates the issue. Whenever you have more than one of something (in this case parents), you essentially have many.

Problem 3 - the relationship between parents and students is many-to-many NOT 1-to-many. Therefore you need three tables to create the appropriate relationship. Or two tables if you choose to keep parents and students in the same table. Whether you choose to put parents and children in the same table is a matter of what data you are storing for each type of person. If it is mostly the same, use one table otherwise use two.

Pat and David,

Thank you for your responses.

I think I get it now. I know on our files (tables), at work, we create a unique key structure and I did start out that way with these fields but changed them grasping at anything to try and make this work!

So based on the info I need for child vs. parent I need 3 files. One with Parent info, one with child info and the relationship table. I am also going to have to have grandparent information but can see where that info is very similar to what goes into the parent file so can use the same table.

I did try a model of your sample Pat, and got the expected results so now just have to determine what I need to add/remove from my existing tables.

Thanks again for your help and explanation of why my structure won't work. I am stubborn about not wanting to change until I know why!!

Cindy

I have made some forward strides but here is another question.

I have now set up 4 tables as follows.

Parent Table:
ParId
ParFstNm
ParLstNm
+other info

Student Table:
StuId
StuFstNm
StuLstNm
+Other Info

Grandparent Table:
GrdID
GrdFstNm
GrdLstNm
+other info

Relative Table:
RelRecTyp
RelID
RelStuID

So I have my relationships set up as follows:

ParTab GrdTab
ParID---- |--GrdId
| RelTab |
|-----> RelID <------|
|------> RelStuID
StuTab |
StuID --|

Now I also need a place to put Siblings. Some siblings are already in the Student table but others are not as they have already graduated or have not started school. What would be the best way to incorporate this into the current structure?
I am trying to not have redundant information in my tables as this causes other problems.

Thanks for your help

Most relationship tables contain only the two foreign key fields that link to either side of the relationship. But sometimes there is information relevant to the intersection. I would suggest a forth table called tblRole. This table would define the roles that members of the "parents" table can play. Then you would add a third field to the relationship table to hold the role. This allows you to connect a child to his mother or father or just parent if you don't want to differentiate. You could also use grandparent or guardian or whatever your application might need as role types.

Pat,

I do have that extra field in the relationship table so that I can link the Parent and/or grandparent to a child. The extra field is based on a table that has parent, grdpar or sibling.

Should I put all the children in the child file and because they would be linked to a parent, use the parent as the "parent" record, if you will, and have a query that displays all the children for a parent without having to show the parent information? Did that make any since?

I have gone back and forth on this as to if I need another table to keep siblings that do not attend school at this point. At this moment I am thinking that I do not need another table. I need to further define my child/student table to include all children regardless if they are students. This way I keep from having redundant data in multiple tables but can still get all the information I need.

I think I just talked myself into this.

Let me know if you see any big holes I may have missed here.

Thanks again for your help.

Cindy

You can't use the relative table to link to either the parent table or grandparent table if you expect to enforce referential integrity. The same foreign key cannot be used to reference multiple tables. You should use a SINGLE table that holds PEOPLE regardless of their relationship to the children. Then in the relative table, you would use the Type field to distinguish between parents/grandparents/aunts/uncles/guardians, etc.

Pat,

Can't I maintain my referential integrity by forcing the Primary key in my grandparent table to always start with say 5 and my parent to always start with a 1? I know that may sound dumb to you but...I am used to having to tell the computer exactly what I want it to do with my data and if it is linked to other files(tables) then I have to make sure the related records are deleted or updated as needed.

I guess I need to set up tables that link to the "adult" file because I don't want to have to enter the same criteria for Grandparents as for Parents and as for siblings as Students.

So I need to set up as follows:?

AdultTab RelTab ChildTab
AdltId---| RelChdID <----- ChdID
AdltType |--> RelAdltID Chdtyp

Now based on the above how do I then set up a link to parent information Vs. Grandparent info. I am trying to put them into different tables as the info will not be the same type for both. Can I do this? Or am I better off putting all the info into the table regardless of if it is parent or grandparent?

My ultimate goal is to (if this is possible) have forms/subforms set up that will allow entry/update of a parent and entry/update of children. I would also like to set up
subforms so that I can look at grandparent and all the Grandchildren, and look at a child and see parent and/or grandparents.

Can I do this through Access?

Thank you in advance
Cindy

Using "intelligent" values as prefixes to unique IDs is NOT the way to inforce referential integrity. Referential integrity can be enforced by Jet if you define your tables properly and specify the appropriate relationships between tables using the relationship window.

You will find that Access is quite capable of doing all that you mentioned in your last paragraph but ONLY if the tables are properly structured.

It might be helpful for you to do some reading on relational database design. Try this to get you started:
http://support.microsoft.com/support/kb/articles/Q288/9/49.ASP

Pat,

Thanks for directing me to the DB info. I appreceiate your help and if you know of any other sites you think might help me on this please pass them along.

Thanks again
Cindy