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 Another Record In The Same Table, Access 2010    
 
   
Ayiramala
post Mar 14 2019, 04:54 AM
Post#1



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


Hi,
I have found this question posted in several other places, but the answers were a bit too difficult for me:

Here's the situation: I have a table (tblStudents). Some of these students may have a brother or sister in another class. So StudentA in Computer Applications may have a brother StudentB in Sociology. StudentC in Sociology may have a sister StudentD in Botany. I want to record this information. Since all the students are in the same tblStudents I just don't know how this could be done.

As always, any help is greatly appreciated. Thanks.
Go to the top of the page
 
DanielPineault
post Mar 14 2019, 05:53 AM
Post#2


UtterAccess VIP
Posts: 6,456
Joined: 30-June 11



Presummably your students table has a PK such as StudentId, you then use another table to store this information




TblRelatives
*************************
StudentId1
StudentId2
Relation (brother/sister/cousin/...)





--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Jeff B.
post Mar 14 2019, 08:10 AM
Post#3


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


Based on your description, can we assume that you have:

tblStudent -- that holds student-specific information
tblSubject -- that holds subject/class-specific information
trelStudentSubject -- that holds info about which student is 'taking' which subject


--------------------
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
 
Ayiramala
post Mar 14 2019, 09:08 AM
Post#4



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


Yes, only I used slightly different names. tblStudents, tblSubjects and tblClasses.

DanielPineault: I did as you suggested, and successfully; but it still remains as a separate table. How do we 'connect' it to tblStudents, may be as a subform, so that this table is available at the time of data entry in tblStudents?
This post has been edited by Ayiramala: Mar 14 2019, 09:29 AM
Go to the top of the page
 
Akudey
post Mar 14 2019, 11:30 AM
Post#5



Posts: 34
Joined: 17-August 13
From: Ghana


QUOTE
but it still remains as a separate table. How do we 'connect' it to tblStudents, may be as a subform, so that

If this is similar to your table setup:
********************************
CODE
tblStudents:     tblSubjects        tblClasses           tblRelatives                                          
---------------  --------------     ------------         ---------------                                          
PK_StudentID     PK_SubjectID       PK_ClassID            PK_RelativeID                                  
Fname            SubjectName        Class (class name)    Relation ' (brother/sister/cousin/...)
Lname            FK_StudentID       FK_SubjectID          FK_StudentID
                                    FK_StudentID

Note this table contains sample records for use as lookup in tblRelatives
CODE
tblRelativeLookUp:
----------------------
ID     Types
1      Brother
2      Sister
3      Cousin
etc..

Then, you can write query and use it as record-source for subform (sfrmRelatives)
CODE
SELCT
    b.Relation, b.FK_StudentID
FROM tblStudents a
LEFT JOIN tblRelatives b
ON a.PK_StudentID = b.FK_StudentID;

Now, place the sub form (sfrmRelatives) on the main form. On the subform property
[Link Master Fields] set it to PK_StudentID of Main form and set [Link Child Fields] to FK_StudentID of subform (sfrmRelative)

************
Note: this codes are not tested
Go to the top of the page
 
Ayiramala
post Mar 14 2019, 10:10 PM
Post#6



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


Thanks. I will try it.

Thanks for all your help. Much appreciated.
Go to the top of the page
 
Akudey
post Mar 15 2019, 06:30 AM
Post#7



Posts: 34
Joined: 17-August 13
From: Ghana


Just a little touch,
****
Please add "FK_StudentID_of_Relation" to tblRelatives to look like this
CODE
tblRelatives                                          
---------------                                        
PK_RelativeID                                  
Relation ' (brother/sister/cousin/...)
FK_StudentID
FK_StudentID_of_Relation

and change this query
CODE
SELECT
    b.Relation, b.FK_StudentID
FROM tblStudents a
LEFT JOIN tblRelatives b
ON a.PK_StudentID = b.FK_StudentID;

***** To this
CODE
SELECT
    b.Relation, b.FK_StudentID, b.FK_StudentID_of_Relation
FROM tblStudents a
LEFT JOIN tblRelatives b
ON a.PK_StudentID = b.FK_StudentID;


Remember, you still need a lookup records from "tblStudents" to
feed "FK_StudentID_of_Relation" column of "tblRelatives"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st March 2019 - 06:58 AM