UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Refer To Other Records In The Same Table, Access 2010    
 
   
Ayiramala
post Jul 7 2019, 08:46 PM
Post#1



Posts: 149
Joined: 16-December 14
From: Kerala, India


Hi,

I have this database of all the students in my school. Everything is working fine, except for one thing:

A student in a certain class may have a brother or sister in another class. How do we enter this information? All the students are in one table tblStudents. So how do we refer to another record in the same table?


Any help is appreciated. Thanks.
Go to the top of the page
 
theDBguy
post Jul 7 2019, 09:01 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,081
Joined: 19-June 07
From: SunnySandyEggo


Hi. You could add a new field, maybe call it Household, to indicate which students are siblings. You can then use this field to refer to them. Also, do you have an ID field in the Students table? If so, this is what you will use to refer to a specific record.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post Jul 7 2019, 09:02 PM
Post#3


UtterAccess VIP
Posts: 9,947
Joined: 25-October 10
From: Gulf South USA


Hi: Not knowing any more about your db or everything you might want to do, my first suggestion would be to include either a family code field in the student table or a separate table of student ids with a family code. With this you should be able to link all students in a family, along with their grade level and other information.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
GroverParkGeorge
post Jul 7 2019, 09:05 PM
Post#4


UA Admin
Posts: 35,544
Joined: 20-June 02
From: Newcastle, WA


How many siblings could there be in each family in your school? Two, three, more?

That determines the more appropriate approach.

I strongly suspect the answer is that there can be more than two, so I'm going to suggest a table of Families, or Households. Assign a HouseholdID or FamilyID to each and add a foreign key for that field in your students table to indicate which Household or Family each student belongs to. That is what we call a one-to-many relationship and it requires both tables.
This post has been edited by GroverParkGeorge: Jul 7 2019, 09:19 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
nvogel
post Jul 8 2019, 12:04 AM
Post#5



Posts: 977
Joined: 26-January 14
From: London, UK


Here is one way to do it:

CREATE TABLE tblSibling
(StudentId1 LONG REFERENCES tblStudent (StudentId),
StudentId2 LONG REFERENCES tblStudent (StudentId)
,PRIMARY KEY (StudentId1,StudentId2));


Go to the top of the page
 
Ayiramala
post Jul 8 2019, 03:59 AM
Post#6



Posts: 149
Joined: 16-December 14
From: Kerala, India


Thank you very much! I never thought of adding a new table for families. I will certainly check it out.

Again, thanks for the really helpful suggestions. much appreciated.
Go to the top of the page
 
GroverParkGeorge
post Jul 8 2019, 07:09 AM
Post#7


UA Admin
Posts: 35,544
Joined: 20-June 02
From: Newcastle, WA


One further thought. I think, for your needs as far as we understand them, a second table with a one-to-many relationship will do for now.

However, I wanted to share my experience with a somewhat similar Access database application I created many years ago for the private school attended by my daughter. We discovered that we actually needed to design this as a many-to-many relationship because of the way "families" can be formed. It turned out that at least one set of siblings in the school were part of TWO different households due to divorce. Parents shared custody of the children. Parents both participated in school activities--individually. So, the students had to be assigned to households for both. That called for THREE tables:

tblHousehold-- the table that stored records for a "household"
tblPerson-- the table that stored records for each person (name, date of birth, etc.)
tblPersonHousehold-- the table that stored the corresponding foreign keys to assign one or more persons (from the person table) to one or more households.

Depending on your specific situation, that may or may not be a better fit than a one-to-many relationship.

And, then we decided to track siblings NOT enrolled in our school because sometimes a big brother or big sister participated in school activities, perhaps as a volunteer or as a chaperone.

So we ended up with still another table for persons who were only in the Student role (i.e. enrolled in classes).

In other words, I would probably invest some additional discovery time here to figure out how much additional development might need to be invested in getting to an appropriate relational design..

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Jeff B.
post Jul 8 2019, 08:00 AM
Post#8


UtterAccess VIP
Posts: 10,271
Joined: 30-April 10
From: Pacific NorthWet


So, a variation on the theme of a "families" table would be to create/use a "related" table. By adding one more (lookup) table (relationship), you could put each pair of persons and their 'relationship' in that new table. Remember that the 'relationship' could be different depending on the direction (i.e., John is Jim's father, Jim is John's son).

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
kfield7
post Jul 8 2019, 08:10 AM
Post#9



Posts: 978
Joined: 12-November 03
From: Iowa Lot


Along these lines, I was designing my own genealogy database, when I decided to download a publicly available one on the Microsoft site.
I quickly found out that that db was quite inadequate, for a reason similar to those presented here.
The main issue I was considering was adoption and/or foster kids, and this is probably even more relevant to the OP's project than to my own.
Adopted / foster kids may have true siblings associated with different parents, and adopted siblings associated with multiple families.
So, does your database need to be able to handle that via natural associations, or will a comment field suffice to handle these as exceptions?
This post has been edited by kfield7: Jul 8 2019, 08:11 AM
Go to the top of the page
 
kfield7
post Jul 8 2019, 08:21 AM
Post#10



Posts: 978
Joined: 12-November 03
From: Iowa Lot


FWIW, my genealogy solution was to create these tables:
persons - any person whatsoever
family - having an autonumber primary key and a natural key comprised of one or two "parents"
relation - showing which persons were "children" in each family. The "child" is flagged as birth, adopted, or foster.
So everything is threaded through the family and relations table.

With this arrangement, multiple marriages would result in multiple families, and individual persons can belong to multiple families.

I don't know if this helps your case, but it might help inspire your ultimate solution.
This post has been edited by kfield7: Jul 8 2019, 08:23 AM
Go to the top of the page
 
Ayiramala
post Jul 10 2019, 09:42 AM
Post#11



Posts: 149
Joined: 16-December 14
From: Kerala, India


Hi,
I tried to include a family table (as suggested by many), and it worked, but then there was another problem:

This solution will work if there are records of a family in that table (obviously). But what if the family is not important? What if all I want to indicate is that the two (or three) students are related? What if family details are unavailable/irrelevant?


Go to the top of the page
 
GroverParkGeorge
post Jul 10 2019, 10:28 AM
Post#12


UA Admin
Posts: 35,544
Joined: 20-June 02
From: Newcastle, WA


You need one family for each person, regardless of whether that family has one sibling or a dozen siblings. A family of one is still a family.

In my own personal database, for example, I have a Household table that consists of two fields: an autonumber primary key and a "HouseholdName" which is usually the last name, or family name, by which that household is known. In my record, for example, "HouseholdName" is "Hepworth"
This post has been edited by GroverParkGeorge: Jul 10 2019, 10:30 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Ayiramala
post Jul 10 2019, 10:55 AM
Post#13



Posts: 149
Joined: 16-December 14
From: Kerala, India


Yes, I understand. That's what I have done now. But I was just wondering if there is another way to relate the records without a 'family name'.
But it doesn't matter. I am happy with the present set-up.

Thanks for all your help. Cheers!
Go to the top of the page
 
GroverParkGeorge
post Jul 10 2019, 11:05 AM
Post#14


UA Admin
Posts: 35,544
Joined: 20-June 02
From: Newcastle, WA


I know of no other way to handle a one-to-many relationship. If the relationship were restricted to two and only two family members, then you could do it with a Self-Join in the table. For example, spouses or partners could be handled that way by including a field called "SpouseID", in which the two person IDs are cross-entered. (Hope that makes sense.) But if the relationship can involve more than two, you have to design the tables accordingly.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
gemmathehusky
post Jul 11 2019, 06:18 AM
Post#15


UtterAccess VIP
Posts: 4,725
Joined: 5-June 07
From: UK


Structures of this nature require careful analysis.
Family situations can vary considerably, and designing a system that can manage every eventuality is likely to become very complex.


Maybe all you might need is table to show that any two children are related, and a note of what that relationship is.

If you have a table:

ChildOne
ChildTwo
Relationship

Then if you have 3 children in the same family or even blended family (ABC), you need 3 entries in this table
AB Sibling, AC HalfSibling and BC HalfSibling.

You probably also need a relationship table to control what entries might be valid.


That may be sufficient, although to find all the relationships for a given child, you will then need to search both the ChildOne and ChildTwo columns.
If you want to do it without searching both columns, then you need to store the reverse entries for each record.

So you store AB, BA, AC, CA, BC, CB, but then it's possible that you get inaccurate data. ie - the relationship value for AB may not be the same as the one that gets entered for BA. You may enter AB, and forget to enter BA. You can control this with your data entry form.

It's a matter of taste I think. Querying two columns and joining (union) them together can be awkward at times. Duplicating data is also awkward.

Anyway, with this sort of setup, for any given child you can easily find all the other children that share some sort of relationship with them.



--------
Edit
I see JeffB also mentioned this idea of storing the "relationship" between 2 children. I thing this idea may actually be the most useful for your situation.

The household idea is also a good one, as it gets over the reversed relationship issue, although you can get into the complex issues with blended families, multiple households and so on, as others have pointed out - which is why modelling this stuff gets so complicated.

Another way is to use the genealogy idea - you store "everybody" - parents and higher ancestors in your genealogy table, and all you need to do is link an individual to each of their parents. Your sibling/half sibling, is someone who shares either both or a single parent. You can find your siblings by simply extracting the other people in the database with the same father and/or mother as yourself. To find other relationships though, such as cousins, and grandparents, you need a recursive search. ie - your grandparents are the parents of your parents. Your cousins are the children of your parents siblings, so you find these by finding first your parents, then finding their siblings, and finally the children of their siblings. As you are unlikely to have the parents and grandparents information in your table it probably isn't possible to do that. This is why animal breeding is much easier - horses don't get divorced and remarry!

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th August 2019 - 12:36 AM