Comparing two values in different rows of a table

[Rex]

Hi,

I want to compare data in two different rows of a table. For eg. I have
a table called twins and each twin has a unique individual ID, for
instance twinA (first born twin) has an ID: 2 whereas twinB (second
born twin) has an ID: 3. both of these records belong to a particular
familyID.

Now I want to be able to compare if sex of both the twins are same or
different

The original table looks something like this:

FamilyID | individual_ID | Name | Sex
---------------------------------------------------------
000 | 02 | john | male
000 | 03 | kasey | female
222 | 02 | jack | male
222 | 03 | ryan | male

I want to have a calculated field which would look at the sex of each
twin belonging to a particular family and tell me where they are same
sex or not. so the new query would look something like this:

FamilyID | individual_ID | Name | Sex | sameSex
--------------------------------------------------------------------------------
000 | 02 | john | male | No
000 | 03 | kasey | female | No
222 | 02 | jack | male | Yes
222 | 03 | ryan | male | Yes

Thanks
Rex

 

[Bill Edwards]

SELECT tblTwin.FamilyId, tblTwin.IndividualId,
tblTwin.Name, tblTwin.Sex, tblTwin_1.Name,
tblTwin_1.Sex, tblTwin_1.IndividualId,
IIf(TblTwin.sex=tbltwin_1.sex,"Same","Different") AS Expr1
FROM tblTwin INNER JOIN tblTwin AS tblTwin_1
ON tblTwin.FamilyId = tblTwin_1.FamilyId
WHERE (((tblTwin.IndividualId)<>[tblTwin_1].[IndividualId]));

 

[Rex]

Bill, the query you have written does not work unfortunately.. Also why
do you have tblTwin_1. This is a bit of concern to me as there is only
one table "twins".

SELECT tblTwin.FamilyId, tblTwin.IndividualId,
tblTwin.Name, tblTwin.Sex, tblTwin_1.Name,
tblTwin_1.Sex, tblTwin_1.IndividualId,
IIf(TblTwin.sex=tbltwin_1.sex,"Same","Different") AS Expr1
FROM tblTwin INNER JOIN tblTwin AS tblTwin_1
ON tblTwin.FamilyId = tblTwin_1.FamilyId
WHERE (((tblTwin.IndividualId)<>[tblTwin_1].[IndividualId]));

Hi,

I want to compare data in two different rows of a table. For eg. I have
a table called twins and each twin has a unique individual ID, for
instance twinA (first born twin) has an ID: 2 whereas twinB (second
born twin) has an ID: 3. both of these records belong to a particular
familyID.

Now I want to be able to compare if sex of both the twins are same or
different

The original table looks something like this:

FamilyID | individual_ID | Name | Sex
---------------------------------------------------------
000 | 02 | john | male
000 | 03 | kasey | female
222 | 02 | jack | male
222 | 03 | ryan | male

I want to have a calculated field which would look at the sex of each
twin belonging to a particular family and tell me where they are same
sex or not. so the new query would look something like this:

FamilyID | individual_ID | Name | Sex | sameSex
--------------------------------------------------------------------------------
000 | 02 | john | male | No
000 | 03 | kasey | female | No
222 | 02 | jack | male | Yes
222 | 03 | ryan | male | Yes

Thanks
Rex

 

[Van T. Dinh]

Need a bit more clarifications:

1. How do you distinguish the multiple sets of twins if a family has more
than 1 set of twins?

2. Any triplet or quad in your database?

 

[Rex]

each individual is recognized based on the individual ID so first set
of twins is given individual ID as 2 and 3 second set of twins have
individual IDs 7 and 8, first set of triplets have individual IDs as 4,
5, 6. Database does not include second set of triplets. It also does
not include quads..

 

[Rex]

I know the database design is very poor.. but I cant change the design
as lot of data has already been entered in the tables..

Rex

 

[Rex]

Hi.. is there anyone who can solve this problem of mine..

 

[Van T. Dinh]

While it may be possible to fudge around with SQL to work out what you want,
you will encounter numerous problems later with the current Table structure
and it is much better to re-structure your Table(s) according to the
Relational Database Design Theory.

 

[Rex]

I know I need to change my table structures.. for which we are
designing a new database from scrach..

For the time being is it possible for you to give me the solution of my
problem. Also where can I read about Relational Database Design Theory

 

[Van T. Dinh]

See the Microsoft Knowledge Base article:

http://support.microsoft.com/kb/283698

Forget about trying to work with current structure. I tried to do a quick
sample SQL but it is simply too messy so I gave up.