Data on Subform

[cindyareed]

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

 

[JCross]

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

 

[cindyareed]

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!!

 

[David R]

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).]

 

[JCross]

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

 

[cindyareed]

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

 

[David R]

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

 

[cindyareed]

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

 

[cindyareed]

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

 

[cindyareed]

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

 

[cindyareed]

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

 

[cindyareed]

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