Relating parent and children records

[Warrior]

I have viewed http://datapigtechnologies.com/flash...combobox2.html and gained some extra ideas but I don't believe the cascading combo boxes is the solution for me (please correct me if I am wrong).

I have created a table with Autonumber, First & Last name fields to contain parents & child names. Now I am trying to use a self join on this table to link the children & (one or both) parents via a form. I have created a query with the main table and 2 alias' of the main table and linked each of these with a left join. Manually putting in the foreign keys works and displays OK in a hack form but what I want to know is "How do I create one form with 3 combo boxes on it that allows me to:"

1. Input a new person - Child or Parent.
2. Input a new child and link it with a new input of parent/s names at the same time.
3. Find a child (via combo box) and link it with a new input of parent/s names.
4. Find a child (via combo box) and link it with an existing parent/s names.

Any help with this would be appreciated, thanks.

[orange]

You may get some info from the data model at
http://www.databaseanswers.org/data_...ogy_dezign.htm

But, you are dealing with a complex set up. You have a hierarchy in the grand parent -- parent --child --grand child...
In reality if you have a table of Parents and a Table for Children, at some point Person X will be a Child AND a Parent... and so the model gets confusing...

For more info see
http://en.wikipedia.org/wiki/Hierarc...database_model
http://www.tdan.com/view-special-features/5400/
http://www.codeproject.com/Articles/...ng-Hierarchies


An attempt by someone with mysql
http://stackoverflow.com/questions/9...mysql-database

You may want to do a little research on GEDCOM http://en.wikipedia.org/wiki/GEDCOM

[Warrior]

Hi Orange, thanks for the leads, I understand it will be complex so I will have a read and see if they are able to assist me. The ultimate use of this DB is for a sports club where the same record can be a player and also a parent/guardian (not going as far as Grand*). By having it all in the same table reduces data input overheads (ie. Having to place a person in the Parent & then the player table). If I can do it all with a Self-Join behind forms (or subforms) then I will be ecstatic! Cheers.

[orange]

Can you post a copy of your database (take out any confidential info)?

My first thought was that you were building a genealogy type database. But since it's sports team with players and parents/guardians I think you have a less complex situation. I would focus on table design and relationships at this point; and have some test data (good and bad) to confirm the model.

Data entry and input form design would be secondary at this time.

Good luck with your project.

[Warrior]

I have a attached a very simplistic db which contains the 1 table and a query which relates the main table with the parent alias' tables.

[orange]

It would be helpful to the reader if you could define/describe the all fields in your table.
What is parentId01, parentid02??
As for your data/test data if you had a list of players, guardians etc and could relate these to your table and query, it would facilitate communications. Even a little paragraph/scenario identifying some players and guardians to help understand what you are doing would help.

You might consider a query(ies) where you could find
-- parent/guardian of player X
-- player who has guardian Y

Does your design do what you need?

Good luck with your project.

[Warrior]

Structure of "Members" table:
"Members" table fields are: MemberID (Primary Key), Player/Guardian Firstname, Player/Guardian Lastname, Parent01ID (Foreign Key), Parent02ID (Foreign Key).
Each of the foreign keys have a Self (Left) join/s to each of the "MemberIDs" (Primary Keys) in the "Members" alias tables called "Parent01" & "Parent02" which are generated via query "Members Q1".

Operation of the "Members" table:
The Members table can contain Parents/Guardians and Players ie.
Example 1. A record may be a player (child or adult) with no child/parent relationships.
Example 2. A record maybe of one adult player (also is Parent/Guardian in this example) that also has one or more children (each having their own separate records linked via left join/s) playing the same sport.
Example 3. A record maybe of one adult player (also is Parent/Guardian in this example - DAD) PLUS another record maybe of one adult player (also is Parent/Guardian in this example - MUM) that also has one or more children (each having their own separate records linked via left join/s) playing the same sport.

Example data in "Members" table:
MemberIDs 1,2,5 are the children of MemberIDs 3 & 4 as referenced by the foreign key fields Parent01ID & Parent02ID.
MemberIDs 11 & 12 have no child/parent relationships.

Form design:
The idea is to create a form from this table that allows me to:
1. Input a new person - Child or Parent.
2. Input a new child and link it with a new input of parent/s names at the same time.
3. Find a child (via combo box) and link it with a new input of parent/s names.
4. Find a child (via combo box) and link it with an existing parent/s names.

By having the one table reduces the data input overheads when Parents are players. ie. Only have to enter same parent/player data in the one table.

Hope you can help, thanks.

[orange]

I'm attempting to get the facts about Players, Parent/Guardians and Children.

Based on your post
- a Player may have no child/parent relationships (example 1)
- an adult player (also a Parent/Guardian ) has one or more children playing the same sport (example 2)
- adult players may be Mums and/or Dads.(example 3)

What is the significance of

playing the same sport

?

Consider an Adult player with no children --are they are in your project scope?

Consider an Adult player with a child - (if the child does not play a sport or not the same sport what does this mean)? Do you have any record of the child?

Consider a Child Player-- must they identify a Parent or Guardian (even if the Parent/Guardian does not play a sport or does not play the same sport as the child) What info do you record?

Consider a Child Player who plays sport x, and a Dad who plays sport Y (and a Mum who plays sport Z) How is this put into your system? If the Mum doesn't play a sport, do you record her as Parent/Guardian?

The key here is to find out who exactly is within the scope of your application, and
how to identify uniquely the "members" in your application and the "roles" they play.

Sometimes having things in one table makes sense. Sometimes trying to put different things into the same table is not not the proper structure. In your example, it still isn't clear to me - yes they can all be "Members", but perhaps Players, Parents, Guardians may need some means of differentiation??

[Warrior]

"Responses to ORANGE questions:

Based on your post
- a Player may have no child/parent relationships (example 1) - CORRECT.
- an adult player (also a Parent/Guardian ) has one or more children playing the same sport (example 2) CORRECT.
- adult players may be Mums and/or Dads.(example 3) - CORRECT.
What is the significance of "playing the same sport?" - ONLY INTERESTED IN MEMBERS OF THIS CLUB AND THE ONE SPORT - LACROSSE.
Consider an Adult player with no children --are they are in your project scope? - YES
Consider an Adult player with a child - (if the child does not play a sport or not the same sport what does this mean)? Do you have any record of the child? - NO, NO NEED TO HAVE THIS CHILD DATA.
Consider a Child Player-- must they identify a Parent or Guardian (even if the Parent/Guardian does not play a sport or does not play the same sport as the child) What info do you record? - YES, REGARDLESS WE NEED CONTACT DETAILS OF PARENT: CLUB RULES.
Consider a Child Player who plays sport x, and a Dad who plays sport Y (and a Mum who plays sport Z) How is this put into your system? If the Mum doesn't play a sport, do you record her as Parent/Guardian? ONLY CONSIDER THOSE PLAYING SAME SPORT AT SAME CLUB. IF MUM IS THE ONLY PARENT THEN WE REQUIRE HER CONTACT DETAILS.
The key here is to find out who exactly is within the scope of your application, and
how to identify uniquely the "members" in your application and the "roles" they play.
Sometimes having things in one table makes sense. Sometimes trying to put different things into the same table is not not the proper structure. In your example, it still isn't clear to me - yes they can all be "Members", but perhaps Players, Parents, Guardians may need some means of differentiation??"

Operation of DB:
We are only talking about the one sport - Lacrosse.
Rules of the Lacrosse club registration are that if you are 18 years or older we only need to register your address, ph number, DOB, etc.
However, if you are under this age we need the names and contact numbers of one or both parents (if available).
If one parent (or both) is already a player/s then we have this detail in the table and a reference/s can be made.
If one (or neither) is not a player then they need to be added and then referenced.
If a new parent comes along with one or more children and they all want to play then they all need to be added and references made.

NOTE: If the children don't play this sport "Lacrosse" for this club then they are not added to the table.

[Warrior]

I am using CAPS only to differentiate the discussions and answers, please don't take this as yelling or shouting at the readers, cheers.

[orange]

No problem with the capitals - I understand.

I tried a few ideas.

You are really dealing with a specific club and 1 sport, so that simplifies some of the combinations/complications.

If you consider all the players, you have 2 groups.
a) those >=18 (no special reqt's, just record the standard data)
b) those < 18 (these require standard info, and they must have 1 or more parent/guardians)

From an entry point of view,
if person is in group a) then record their info, or update their info as necessary

if person is in group b) then record their specific info/or update as necessary, then
(since parent/guardian is required) - to record the parent / guardian(s)
first see if an existing player >18 is an appropriate parent/guardian
have a form with a listbox for players under18, a listbox for players 18 or older, and a combo (Mum, Dad,Other)
Select the player in group a, the player in group b, and the relationship from the combo and have a button to save the record

I had the following code to save a record

Private Sub btnSave_Click()
   On Error GoTo btnSave_Click_Error

On Error GoTo Err_btnSave_Click
Dim sql As String
sql = "Insert into tblParentGuardian (IsGuardianOf,IsPlayerId,IsRelatedAs,LastUpdateDate) " _
   & "Values (" & Me.List0.Value & "," & Me.List2.Value & ",'" & Me.Combo5.Value & "',#" & Date & "#)"

    Debug.Print sql
CurrentDb.Execute sql, dbFailOnError
Exit_btnSave_Click:
    Exit Sub

Err_btnSave_Click:
    MsgBox Err.Description
    Resume Exit_btnSave_Click

   On Error GoTo 0
   Exit Sub

btnSave_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure btnSave_Click of VBA Document Form_Form1"
    
End Sub

If the player <18 identifies a parent/guardian who is not a player, them you would insert relevant info in the tblParentGuardian.

Just some thoughts for consideration.
I have attached layouts for tblPlayer and tblParentGuardian and the initial form